sql根据一个表数据修改另外一个表数据

UPDATE warehouse
SET warehouse.salesman_id = ( SELECT salesman_id FROM salesman_warehouse_rel WHERE warehouse.id = salesman_warehouse_rel.warehouse_id )

https://blog.csdn.net/hcwbr123/article/details/105313934/

现象：
sql根据一个表数据修改另外一个表数据

方法：
1、根据表tb_b 设置表tb_a 的date 字段
取值tb_b的pay_time tb_b对应tb_a为一对多的关系 这里取值多条中的最大值

UPDATE tb_a a INNER JOIN
tb_b b ON b.id=a.order_id
SET a.date = (SELECT MAX(pay_time) FROM tb_b b WHERE b.id=a.order_id);

1
2
3
4
5
2、根据表tb_b设置表tb_a的time参数
这里加入了null值判断 取值对应关系中tb_b的最大pay_time的值

UPDATE
tb_a s
SET
s.time = IFNULL((SELECT MAX(t.pay_time) FROM tb_b t WHERE t.id = s.order_id AND t.stock_id=s.business_id),s.process_time)
WHERE s.code = 'D36';
1
2
3
4
5
6
3、根据表tb_b 和tb_pay 来更新表tb_a 的多个字段
first_date 直接根据 tb_b取值最大值
first_time 根据tb_b 和tb_pay 来取值tb_pay 的pay_time参数

UPDATE tb_a sa INNER JOIN
tb_b tp ON tp.d=sa.order_id
SET sa.first_date = (SELECT MAX(pay_time) FROM tb_b tp WHERE tp.id=sa.order_id AND tp.status='2' AND tp.TYPE='1'),
sa.first_time = (SELECT GROUP_CONCAT(tpm.pay_time) FROM tb_b tp LEFT JOIN tb_pay tpm ON tpm.id=tp.merge_id WHERE tp.reg_id=sa.order_id AND tp.status='2' AND tp.TYPE='1'),
sa.first_bank_date =(SELECT GROUP_CONCAT(tpm.bank_date) FROM tb_b tp LEFT JOIN tb_pay tpm ON tpm.id=tp.merge_id WHERE tp.reg_id=sa.order_id AND tp.status='2' AND tp.TYPE='1');

1
2
3
4
5
6
文章知识点与官方知识档案匹配，可进一步学习相关知识
MySQL入门技能树SQL高级技巧CTE和递归查询13273 人正在系统学习中