Update column based on two column values with many combinations-CodePudding

I want to update a single column based on two columns one way I tried using this

UPDATE Table_name
    Set column_name=1 
WHERE col_1 = 120 and col_2 = 1991110

I tried using case expression

UPDATE table_name
    SET column_name = 
        CASE 
        when col_1 = 2 and col_2 = 1991111 then 2
        when col_1 = 3 and col_2 = 1991110 then 3
        .
        .
        .
        .
        .
        .
        else 0
        end

But the problem is I have 380 combinations of col_1 and col_2 so if I use this approach I would be using it 380 times. I want some suggestions.

Expected Output:

col1     col2      col3
1       1991110     1
2       1991111     2
3       1991110     3
4       1991111     4

CodePudding user response：

The 380 possible update combinations belongs in a separate table. For the purpose of illustration, we can do an update join here to a subquery containing the combinations:

UPDATE t1
SET column_name = t2.val
FROM table_name t1
INNER JOIN
(
    SELECT 2 AS col_1, 1991111 AS col_2, 2 AS val UNION ALL
    SELECT 3, 1991110, 3 UNION ALL
    ...
) t2
    ON t2.col_1 = t1.col_1 AND t2.col_2 = t1.col_2;

The subquery aliased as t2 above can be maintained a separate bona-fide table, for convenience.