How to Upsert Multiple Rows with Individual Values in One Statement

INSERT INTO t1 (a, b, c, d)
VALUES (...) 
ON     CONFLICT (a,b) DO UPDATE
SET    d = EXCLUDED.d;          -- that's all !!!

CREATE TABLE t1 (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
column2 INT NOT NULL,
column3 INT NOT NULL);

INSERT INTO t1
VALUES
(1, 1, 10),
(7, 2, 20);

UPDATE t1 
JOIN ( SELECT
1 AS id,0 AS col2,2 AS col3 UNION
SELECT 5,0,3 UNiON
SELECT 7,0,4) t2 ON t2.id = t1.id
 
 SET t1.column3 = t2.col3;
 

SELECT * FROM t1;

id	column2	column3
1	1	2
7	2	4

UPDATE multiple rows with different values in one query in MySQL

You can do it this way:

UPDATE table_users
    SET cod_user = (case when user_role = 'student' then '622057'
                         when user_role = 'assistant' then '2913659'
                         when user_role = 'admin' then '6160230'
                    end),
        date = '12082014'
    WHERE user_role in ('student', 'assistant', 'admin') AND
          cod_office = '17389551';

I don't understand your date format. Dates should be stored in the database using native date and time types.

SQL - Update multiple records in one query

Try either multi-table update syntax

UPDATE config t1 JOIN config t2
    ON t1.config_name = 'name1' AND t2.config_name = 'name2'
   SET t1.config_value = 'value',
       t2.config_value = 'value2';

Here is a SQLFiddle demo

or conditional update

UPDATE config
   SET config_value = CASE config_name 
                      WHEN 'name1' THEN 'value' 
                      WHEN 'name2' THEN 'value2' 
                      ELSE config_value
                      END
 WHERE config_name IN('name1', 'name2');

Here is a SQLFiddle demo

Update multiple rows with different values in a single SQL query

There's a couple of ways to accomplish this decently efficiently.

First -

If possible, you can do some sort of bulk insert to a temporary table. This depends somewhat on your RDBMS/host language, but at worst this can be accomplished with a simple dynamic SQL (using a VALUES() clause), and then a standard update-from-another-table. Most systems provide utilities for bulk load, though

Second -

And this is somewhat RDBMS dependent as well, you could construct a dynamic update statement. In this case, where the VALUES(...) clause inside the CTE has been created on-the-fly:

WITH Tmp(id, px, py) AS (VALUES(id1, newsPosX1, newPosY1), 
                               (id2, newsPosX2, newPosY2),
                               ......................... ,
                               (idN, newsPosXN, newPosYN))

UPDATE TableToUpdate SET posX = (SELECT px
                                 FROM Tmp
                                 WHERE TableToUpdate.id = Tmp.id),
                         posY = (SELECT py
                                 FROM Tmp
                                 WHERE TableToUpdate.id = Tmp.id)

WHERE id IN (SELECT id
             FROM Tmp)

(According to the documentation, this should be valid SQLite syntax, but I can't get it to work in a fiddle)

Update a single column on multiple rows with one SQL query

You can actually do it using insert into ...on duplicate key update

insert into [table](ID,DATA) 
values(23,'FOO'),(47,'ASD'),(54,'DSF')..,
on duplicate key update DATA=values(DATA)

Update multiple rows using single value

You can use IN It should be fine for 50 values. If you expect hundreds or thousands of values then you should find some other way like temp table.

UPDATE    myTable
SET       MailingDate = CONVERT(DATETIME, '2013-09-01 00:00:00', 102)
WHERE     tblID IN ( 1, 2,3,...)

If you are picking your ids from the result of another query, you can even replace the entire list with the subquery in IN

Or if your number is in sequence, you can also do

UPDATE    myTable
SET       MailingDate = CONVERT(DATETIME, '2013-09-01 00:00:00', 102)
WHERE     tblID >= 1  AND tblID <= 50

---

Related Topics