pgsql Update 根据B表更新A表

pgsql Update 根据B表更新A表，A表不能加别名

如下SQL会报错：

update tablename1  aa 
set aa.sort = bb.sort
from (select id,row_number() OVER(order by id )  sort 
      from tablename1  order by id desc)  bb
where aa.id = bb.id 

需改为：

update tablename1  aa 
set  sort = bb.sort
from (select id,row_number() OVER(order by id )  sort 
      from tablename1  order by id desc)  bb
where aa.id = bb.id 

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’)