Re: [SQL ] update multiple-table 語法求教
對不起，我自問自答：
我明白了，句子沒有錯
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
討論串 (同標題文章)
以下文章回應了本文：
完整討論串 (本文為第 4 之 5 篇)：