UPDATE cust_his ch LEFT OUTER JOIN project_corp pc
ON pc.PROJECT_ID = ch.PROJECT_ID AND pc.IS_MAIN = 'Y'
SET ch.del_flag = #{DEL_FLAG_DELETE}
WHERE ch.CUST_ID = #{custId}
OR pc.CUST_ID = #{custId}
以上为修改前的SQL语句(MySQL)
首先想要改写为可在Oracle中实现相同功能的sql,需要先知道修改的那些数据
即
SELECT * FROM cust_his ch LEFT OUTER JOIN project_corp pc ON pc.PROJECT_ID = ch.PROJECT_ID AND pc.IS_MAIN = 'Y'
WHERE ch.CUST_ID = #{custId}
OR pc.CUST_ID = #{custId}
以上的查询语句可得到要修改的条数
那么,我们在Oracle中只需要对以上查询记录进行修改就可得到MySQL中的update join的效果
这里我用id进行记录选定
UPDATE cust_his ch
SET ch.del_flag = #{DEL_FLAG_DELETE}
WHERE ch.ID IN
(SELECT
ch.ID
FROM
cust_his ch LEFT OUTER
JOIN project_corp pc ON pc.PROJECT_ID = ch.PROJECT_ID
AND pc.IS_MAIN = 'Y'
WHERE
ch.CUST_ID = #{custId}
OR pc.CUST_ID = #{custId} )
这样就在Oracle中实现了MySQL中的update join的效果