mysql关联表更改表多个字段值

关联表更改某一个字段值：更改部分数据，外层where条件控制更改记录的数量

BEGIN;
UPDATE sturgeon_summary s SET s.set_date = (SELECT set_date FROM sturgeon_set_pay p WHERE s.set_no = p.set_no ) WHERE s.merchant_no ="300900100390" ;
SELECT * FROM sturgeon_summary WHERE merchant_no ="300900100390";
ROLLBACK;
-- COMMIT;

 

关联表更改某一个字段值：更改全部记录

BEGIN;
UPDATE sturgeon_summary s SET s.merchant_no = (SELECT merchant_no FROM sturgeon_set_pay p WHERE s.set_no = p.set_no );
SELECT * FROM sturgeon_summary;
ROLLBACK;
-- COMMIT;

关联表更改多个字段值：

BEGIN;
UPDATE sturgeon_summary s SET s.set_date = (SELECT set_date FROM sturgeon_set_pay p WHERE s.set_no = p.set_no ),s.merchant_no = (SELECT merchant_no FROM sturgeon_set_pay p WHERE s.set_no = p.set_no )  WHERE s.set_no ="M2201904290100100010039093733702";
SELECT * FROM sturgeon_summary;
ROLLBACK;
-- COMMIT;