oracle 同时更新多表,Oracle Update多表关联更新

项目中用到了Oracle Update多表关联更新，简单记录一下

写法一：

update TBL_COMPANY_USER card

set card.status = 9

where card.company_id =

(select company.ID

from TBL_COMPANY_INFO company

where company.CODE = '10001')

and card.cp_id =

(select cp.ID from TBL_CP_INFO cp where cp.CODE = '01')

and card.card_face_no = '10756415700'

写法二：

update TBL_COMPANY_USER card

set card.status = 2

where exists (select *

from TBL_COMPANY_INFO tcoi, TBL_CP_INFO tcpi

where card.company_id = tcoi.id

and card.cp_id = tcpi.id

and tcoi.code = '10001'

and tcpi.code = '01'

and card.card_face_no = '10756415700')

写法三：

UPDATE table_1 a

SET col_x1 = (SELECT b.col_y1, b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m),

col_x2 = (SELECT b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m)

WHERE EXISTS (SELECT * FROM table_2 b WHERE b.col_n = a.col_m)

或

UPDATE table_1 a

SET col_x1 = (SELECT b.col_y1, b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m),

col_x2 = (SELECT b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m)

WHERE a.col_m=(SELECT b.col_n FROM table_2 b WHERE b.col_n = a.col_m)

写法四：

UPDATE table_1 a

SET (col_x1, col_x2) = (SELECT b.col_y1, b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m)

WHERE EXISTS (SELECT * FROM table_2 b WHERE b.col_n = a.col_m);

或

UPDATE table_1 a    SET (col_x1, col_x2) = (SELECT b.col_y1, b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m) WHERE a.col_m=(SELECT b.col_n FROM table_2 b WHERE b.col_n = a.col_m)