Update multiple rows with different values in SQL

Question

I have a table like this:

SKU            Size
A              10
B              10
C              10
D              10
E              10
F              10
G              10

I want to change it to:

SKU            Size
A              20
B              10
C              30
D              10
E              80
F              10
G              60

I have more than 3000 rows of records to update. How can I do that with SQL update command ?

Answer 1

UPDATE T
SET Size = CASE SKU
    WHEN 'A' THEN 20
    WHEN 'B' THEN 10
    WHEN 'C' THEN 30
    WHEN ...
END

Or there may be a formula for calculating the size, but you've failed to give it in your question (Or we may have to switch to a more complex CASE expression, but again, too little detail in the question).

Answer 2

Create a table with the mapping of SKU to new size; update the master table from that.

Many dialects of SQL have a notation for doing updates via joined tables. Some do not. This will work where there is no such notation:

CREATE TABLE SKU_Size_Map
(
     SKU     CHAR(16) NOT NULL,
     Size    INTEGER NOT NULL
);
...Populate this table with the SKU values to be set...
...You must have such a list...

UPDATE MasterTable
   SET Size = (SELECT Size FROM SKU_Size_Map
                WHERE MasterTable.SKU = SKU_Size_Map.Size)
 WHERE SKU IN (SELECT SKU FROM SKU_Size_Map);

The main WHERE condition is need to avoid setting the size to null where there is no matching row.

You can probably also do it with a MERGE statement. But the key insight for any of these notations is that you need a table to do the mapping between SKU and size. You either need a table or you need an algorithm, and the sample data doesn't suggest an algorithm.