第一种写法
update table1 o
set(column1,column2,column3,column4,column5)=(select '是',aa.column22,aa.column33,aa.column44,aa.column55 from
(SELECT a.id, tt.column22, tt.column33,tt.column44, tt.column55 FROM table2 tt INNER JOIN table1 a on tt.id = a.id)
aa where aa.id=o.id AND o.DATE_MONTH='202208' and rownum<2)
where exists(
SELECT count(1)FROM table2 tt INNER JOIN table1 a on tt.id = a.id and a.DATE_MONTH='202208'
);
commit;
注:
(SELECT a.id, tt.column22, tt.column33,tt.column44, tt.column55 FROM table2 tt INNER JOIN table1 a on tt.id = a.id)
aa where aa.id=o.id AND o.DATE_MONTH=‘202208’ and rownum<2)
表示当查询到多条数据时,取第一条数据
第二种写法
-- 这是另一种更新语句
merge into table1 o
using (SELECT a.id,'是' as column1, tt.column2, tt.column3,tt.column4, tt.column5 FROM table2 tt
INNER JOIN table1 a on tt.id = a.id where rownum<2) aa
on (o.id=aa.id AND o.DATE_MONTH='202208')
when matched then update set o.column1=aa.column1 ,o.column2=aa.column2 ,o.column3=aa.column3
,o.column4=aa.column4,o.column5=aa.column5;
注:
(SELECT a.id,‘是’ as column1, tt.column2, tt.column3,tt.column4, tt.column5 FROM table2 tt
INNER JOIN table1 a on tt.id = a.id where rownum<2)
表示当查询到多条数据时,取第一条数据