|
项目中用到了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,
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,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) （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
