❤️sql根据多表联查后的结果集更新一个表

小明的学习圈子2023-11-27数据库

mysql 根据多表联查后的结果集更新一个表

UPDATE [要更新的表名] [别名]
INNER JOIN [关联的表名] [关联的别名] ON [关联条件] 
SET [要更新的字段] = [字段值] 
WHERE
    [限制条件的列] = [限制条件]

mysql例子

UPDATE UPDATE_TABLE_SOURCE UTS
INNER JOIN (
    SELECT
        ( T5.COL1 - T5.COL1 ) AS COL1,
        TEMP.T4_SID 
    FROM
        (
        SELECT
            T4.SID AS T4_SID,
            SUM( T1.COL2 ) AS COL2,
            IFNULL( T4.COL1, 0 ) COL3,
            T4.ID 
        FROM
            TABLE1 T1
            INNER JOIN TABLE2 T2 ON T1.ID = T2.ID
            INNER JOIN TABLE3 T3 ON T1.TABLE3_SID = T3.SID 
            AND T3.DEL_FLAG = '0'
            LEFT JOIN TABLE4 T4 ON T4.ID = T3.ID 
            AND ( ( T3.COL5 IS NOT NULL AND T2.COL8 = T4.COL10 ) OR ( T3.COL5 IS NULL ) ) 
            AND T4.DEL_FLAG = '0' 
        WHERE
            T1.DEL_FLAG = '0' 
            AND ( T1.COL2 > 0 OR T4.COL1 > 0 ) 
        GROUP BY
            T3.COL5,
            T1.UNIT,
            T2.COL8,
            T4.COL1 
        ) AS TEMP
        LEFT JOIN TABLE5 T5 ON T5.COL8 = TEMP.COL10 
        AND T5.DEL_FLAG = '0' 
    WHERE
        ( TEMP.COL2 - TEMP.COL3 ) < 0 
    ) T ON UTS.SID = T.T4_SID 
    SET UTS.COL1 = ( UTS.COL1 - T.COL1 ) 
WHERE
    UTS.SID = T.T4_SID

oracle例子

UPDATE TGA_CHANNEL T1
INNER JOIN ( SELECT DOOR_NAME FROM TGA_DOOR ) T ON T1.DOOR_ID = T.DOOR_ID 
SET T1.DOOR_NAME = T.DOOR_NAME 
WHERE
    T1.DOOR_ID = T.DOOR_ID 这个sql报错ORA-00971: missing SET keyword

问题在于,Oracle不支持使用INNER JOINUPDATE语句中。你需要使用子查询或者MERGE语句来实现这个功能。
以下是使用子查询的方法:
UPDATE TGA_CHANNEL T1
SET T1.DOOR_NAME = (SELECT T.DOOR_NAME FROM TGA_DOOR T WHERE T1.DOOR_ID = T.DOOR_ID)
WHERE EXISTS (SELECT 1 FROM TGA_DOOR T WHERE T1.DOOR_ID = T.DOOR_ID)
这个语句会更新TGA_CHANNEL表中的DOOR_NAME字段,将其设置为TGA_DOOR表中对应的DOOR_NAME值。
如果你想使用MERGE语句,可以参考以下的例子:
MERGE INTO TGA_CHANNEL T1
USING (SELECT DOOR_ID, DOOR_NAME FROM TGA_DOOR) T
ON (T1.DOOR_ID = T.DOOR_ID)
WHEN MATCHED THEN UPDATE SET T1.DOOR_NAME = T.DOOR_NAME
Last Updated 2024/4/6 10:47:15