Update multiple rows with a single query in PostgresSQL-CodePudding

I am trying to update multiple rows with a single query using Postgres. Here is what I am trying to do: If the sku is 0001, then i want to update field_1 to foo. Repeat with all the other skus.

When I run this code, this code correctly updates the correct row and field. BUT it turns all the other records' field_1 into null. What code should be added here to prevent that?

UPDATE table
    SET field_1 = ( case WHEN sku = '0001' then 'foo'
                         WHEN sku = '0002' then 'bar'
                         WHEN sku = '0003' then 'baz'
                    END
                   )

BEFORE running the query

sku	field_1
0001	dummy_1
0002	dummy_2
0003	dummy_3
0004	dummy_4
0005	dummy_5
0006	dummy_6

AFTER running the query

sku	field_1
0001	foo
0002	bar
0003	baz
0004	null
0005	null
0006	null

CodePudding user response：

Add a WHERE clause which restricts the SKUs targeted for update:

UPDATE table
SET field_1 = CASE sku WHEN '0001' THEN 'foo'
                       WHEN '0002' THEN 'bar'
                       WHEN '0003' THEN 'baz' END
WHERE sku IN ('0001', '0002', '0003');