CODEWITHSUNDEEP
sql
Shades
Shades

Reputation: 285

Update multiple table rows in a column with different values

I have multiple rows i need to update and insert customer names. How can I do this in one query as opposed to running something like the below query again and again with different names? 

UPDATE orders
SET cust_name = 'Sue'
WHERE p_id = 6

Upvotes: 2

Views: 2118

Answers (2)

Confusion
Confusion

Reputation: 16841

You generally can't. At least, MySQL, DB2, SQL Server and PostgreSQL don't support queries where different WHERE clauses apply to different SET clauses.

You also don't need to. What you usually can do is use a prepared statement with placeholders for the variables, that you execute with a list of tuples of parameters. Depending on the language, database adapter and database, this may be executed in 'batch' mode, which is quite efficient.

More precision requires more information about the language, database and database adapter you are using.

Upvotes: 0

Yassir Ennazk
Yassir Ennazk

Reputation: 7169

You can use something like

    UPDATE mytable
    SET myfield = CASE other_field
        WHEN 1 THEN 'value'
        WHEN 2 THEN 'value'
        WHEN 3 THEN 'value'
    END
    WHERE id IN (1,2,3)

For more info check Update Multiple Rows With Different Values and a Single SQL Query

Upvotes: 3

Related Questions