能否在单条UPDATE语句中实现多SET操作？适配SQL Server/Oracle/DB2

Great question! You absolutely can combine those two UPDATE statements into a single one using conditional logic, though the exact syntax will differ a bit between SQL Server, Oracle, and DB2 since each database has its own quirks for joins and conditional updates. Let's break down the solution for each platform:

SQL Server #

Use a LEFT JOIN to cover both matched and unmatched rows, paired with COALESCE to pick between TABLE2 values or your constants. We replace Oracle's ROWNUM <10 with TOP (9) to limit the update to the first 9 rows:

UPDATE TOP (9) t1
SET 
    COL1 = COALESCE(t2.ATTRIBUTE1, '123-4567890-1'),
    COL2 = COALESCE(t2.ATTRIBUTE2, '0000000000'),
    COL3 = COALESCE(t2.ATTRIBUTE3, 'CONSTANT FULL NAME')
FROM TABLE1 t1
LEFT JOIN TABLE2 t2 
    ON t1.COL1 = t2.PID1 
    AND t1.COL2 = t2.PID2;

Explanation: The LEFT JOIN includes all rows from TABLE1 regardless of matches in TABLE2. COALESCE grabs the first non-null value—so if there's a match, it uses the TABLE2 attributes; if not, it falls back to your constant values.

Oracle #

Oracle's MERGE statement is ideal here, as it efficiently handles join-based updates and conditional logic in one step. We'll include ROWNUM to replicate your original row limit:

MERGE INTO TABLE1 t1
USING (
    SELECT 
        t1.COL1 AS orig_col1,
        t1.COL2 AS orig_col2,
        t2.ATTRIBUTE1,
        t2.ATTRIBUTE2,
        t2.ATTRIBUTE3,
        ROWNUM AS rn
    FROM TABLE1 t1
    LEFT JOIN TABLE2 t2 
        ON t1.COL1 = t2.PID1 
        AND t1.COL2 = t2.PID2
) src
ON (t1.COL1 = src.orig_col1 AND t1.COL2 = src.orig_col2)
WHEN MATCHED THEN
    UPDATE SET 
        COL1 = NVL(src.ATTRIBUTE1, '123-4567890-1'),
        COL2 = NVL(src.ATTRIBUTE2, '0000000000'),
        COL3 = NVL(src.ATTRIBUTE3, 'CONSTANT FULL NAME')
WHERE src.rn < 10;

Explanation: The subquery combines TABLE1 and TABLE2 with a LEFT JOIN and adds a row number to limit updates. NVL (Oracle's equivalent of COALESCE) handles the switch between matched values and constants, and MERGE applies the update to TABLE1.

DB2 #

DB2 supports updating derived tables, which lets us combine the join, row limit, and conditional update in one statement:

UPDATE (
    SELECT 
        t1.COL1, t1.COL2, t1.COL3,
        t2.ATTRIBUTE1, t2.ATTRIBUTE2, t2.ATTRIBUTE3
    FROM TABLE1 t1
    LEFT JOIN TABLE2 t2 
        ON t1.COL1 = t2.PID1 
        AND t1.COL2 = t2.PID2
    FETCH FIRST 9 ROWS ONLY
) upd
SET 
    COL1 = COALESCE(upd.ATTRIBUTE1, '123-4567890-1'),
    COL2 = COALESCE(upd.ATTRIBUTE2, '0000000000'),
    COL3 = COALESCE(upd.ATTRIBUTE3, 'CONSTANT FULL NAME');

Explanation: We first create a derived table that joins TABLE1 and TABLE2 (including all TABLE1 rows) and limits to the first 9 rows with FETCH FIRST 9 ROWS ONLY. Then we update this derived table using COALESCE to handle both matched and unmatched cases.

Just make sure to test this in a non-production environment first to verify it behaves exactly like your original two statements!

内容的提问来源于stack exchange，提问作者Vivek Harry