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

现象：
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);

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';

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');
    

4、给表A某内容添加新内容
update tb_a set name=concat(name,‘_test1’)

5、根据ABC关系更新A表json数组字段内容

UPDATE A  
SET A.json_data = (  
    SELECT JSON_ARRAYAGG(CAST(C.id AS CHAR))  
    FROM (  
        SELECT id, name   
        FROM B   
        WHERE B.deleted = 0  
    ) C  
    JOIN (  
        SELECT id, name   
        FROM C
        WHERE c.deleted = 1  
    ) B ON B.name = C.name  
    WHERE JSON_CONTAINS(A.json_data, CAST(B.id AS CHAR))  
)  
WHERE A.deleted = 0;