CODEWITHSUNDEEP
sql
ivan ivanov
ivan ivanov

Reputation: 11

SQL update multiple values

i have table ArticleSubGroup with some data and i want to update three columns (margin, minimum Margin,maximumMargin) with diffrent values for every id. I can use this update query but i have more than 3000 id's.

update articlesubgroup set margin=15, minimumMargin=5, maximumMargin=25 where id=1
update articlesubgroup set margin=10, minimumMargin=5, maximumMargin=15 where id=2
..........................
update articlesubgroup set margin=20, minimumMargin=15, maximumMargin=30 where id=3000

Is there any way to update the data with a simple query (by first listing the values ​​and then all the IDs

Upvotes: 1

Views: 209

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

For a small number of id values, you may use a CASE expression here:

UPDATE articlesubgroup
SET
    margin = CASE id WHEN 1 THEN 15
                     WHEN 2 THEN 10 ... END,
    minimumMargin = CASE id WHEN 1 THEN 5
                            WHEN 2 THEN 5 ... END,
    maximumMargin = CASE id WHEN 1 THEN 25
                            WHEN 2 THEN 15 ... END
WHERE
    id IN (1, 2, ...);

If you really have thousands of id values, this implies that your data should really be in another table somewhere. Assuming such a table exists:

id | margin | minimumMargin | maximumMargin
1  | 15     | 5             | 25
2  | 10     | 5             | 15

then you can simply do an update join:

UPDATE t1
SET
    t1.margin = t2.margin,
    t1.minimumMargin = t2.minimumMargin,
    t1.maximumMargin = t2.maximumMargin
FROM articlesubgroup t1
INNER JOIN update_table t2
    ON t1.id = t2.id;

The above update join code is for SQL Server, and would vary if you are using a different database. The basic point here is that a one line option is not good if you have many different id values to cover. Instead, get that data into a proper table and use joins.

Upvotes: 1

Related Questions