oracle update left join 写法 (修改某列，条件字段在关联表中)

案例:

E：考核表 X,：用户表

USERNAME 关联

需求:修改营业部最高分

分析:通过登录账号的营业部ORG_ID 更新E表,E表中无ORG_ID 字段，X表中可以关联.

写法一:

UPDATE USER_INFO E

SET E.TOP_SCORE =

(SELECT 230

FROM USER X

WHERE E.USERNAME = X.USERNAME

and X.ORG_ID = ‘B26603C4BB9B0042E0430ABC694C0042’)

WHERE E.USERNAME = (SELECT X.USERNAME

FROM USER X

WHERE E.USERNAME = X.USERNAME

and X.ORG_ID = ‘B26603C4BB9B0042E0430ABC694C0042’)

写法二:

UPDATE USER_INFO E

SET E.TOP_SCORE = 230

WHERE exists (SELECT X.USERNAME

FROM USER X

WHERE E.USERNAME= X.USERNAME

and X.ORG_ID = ‘B26603C4BB9B0042E0430ABC694C0042’)

写法三:

UPDATE USER_INFO E

E.TOP_SCORE = 230

WHERE E.USERNAME in(

SELECT X.USERNAME FROM USER X WHERE

E.USERNAME =X.USERNAME and

X.ORG_ID=’B26603C4BB9B0042E0430ABC694C0042’)

---------------------

作者：guidestarzh

来源：CSDN

原文：https://blog.csdn.net/guidestarzh/article/details/81509058

版权声明：本文为博主原创文章，转载请附上博文链接！