Re: [SQL ] update multiple-table 語法求教

看板Database (資料庫)作者jonce007時間8年前 (2016/08/14 15:06)推噓0(0推 0噓 0→)

留言0則, 0人參與討論串4/5 (看更多)

對不起，我自問自答：我明白了，句子沒有錯 Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 matched: 符合條件有1筆因為要更改的值和原來一樣，所以changed=0 先歸零一下再跑： update pb1 set opi=0, oyd=0, okg=0; UPDATE pb1 join (select pbin, sum(opi) pi , sum(oyd) yd, sum(okg) kg from pb1 where pbin='020417008' &&pbon<>'' group by pbin) AS pb2 SET pb1.opi=pb2.pi , pb1.oyd=pb2.yd , pb1.okg=pb2.kg WHERE pb1.pbin=pb2.pbin && pb1.pbon=''; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 所以只是我耍白痴，看不懂mysql訊息而己XD ------------------------------------------------------------------------- 我不懂的是：如果 select pb1.pbin,pb2.pbin,pb1.ipi,pb1.iyd, pb1.ikg, pb2.pi, pb2.yd, pb2.kg from pb1 join (select pbin, sum(opi) pi , sum(oyd) yd, sum(okg) kg from pb1 where pbon<>'' &&pb1.pbin='020417008'group by pbin) AS pb2 on pb1.pbin=pb2.pbin WHERE pb1.pbon='' ; +-----------+-----------+------+------+------+------+------+------+ | pbin | pbin | ipi | iyd | ikg | pi | yd | kg | +-----------+-----------+------+------+------+------+------+------+ | 020417008 | 020417008 | 51.0 | 6810 | 0.00 | 51.0 | 6810 | 0.00 | +-----------+-----------+------+------+------+------+------+------+ 得到我想到的數據，一模一樣的句子拿來做update update pb1 join (select pbin, sum(opi) pi , sum(oyd) yd, sum(okg) kg from pb1 where pbon<>'' &&pb1.pbin='020417008'group by pbin) AS pb2 on pb1.pbin=pb2.pbin set pb1.oyd=pb2.yd ,pb1.opi=pb2.pi ,pb1.okg=pb2.kg WHERE pb1.pbon='' ; 得到的卻是： Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 matched 和 changed 的不同關鍵在那裡？ -- ※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 111.241.224.25 ※ 文章網址: https://www.ptt.cc/bbs/Database/M.1471158388.A.C53.html ※ 編輯: jonce007 (111.241.224.25), 08/14/2016 16:24:52 ※ 編輯: jonce007 (111.241.224.25), 08/14/2016 16:31:09