952
社区成员
发帖
与我相关
我的任务
分享
drop table IF EXISTS tb1;
drop table IF EXISTS tb2;
create TABLE tb1(
ids int,
score INT);
CREATE TABLE tb2(
ida int,
scorea INT);
INSERT INTO tb1 VALUES(1,10),(2,20),(3,30);
INSERT INTO tb2 VALUES(1,100),(2,200);
select * from tb1;
select * from tb2;
UPDATE tb1 SET score = COALESCE( b.scorea,0) from tb1 a
left join tb2 b on a.ids=b.ida ;
select * from tb1;
select * from tb2;
UPDATE tb1 SET score = COALESCE( b.scorea,0) from tb1 a
left join tb2 b on a.ids=b.ida
returning a.ids, b.ida, tb1.ids;
所以正确的写法是:
UPDATE tb1 SET score = COALESCE( b.scorea,0) from tb1 a
left join tb2 b on a.ids=b.ida
where a.ids = tb1.ids;