Update multiple rows at once in MySQL

Question

I have a vector of values

vector = [ 10, 11, 10, 11, 12 ];

and an initialized database table

My goal is to update the value of each row ( which is initialized to -1 ) with a value from the vector. So the id =1, will have value 10, the id = 2, value 11 etc.

I can do this in a loop, ie for every value in vector update the corresponding row.

However I was wondering if I can do this with one update statement. I am using MySQL.

The final state after the update should be:

The Nth row (sorted) in the database should have the Nth value of the vector.

Is the id a primary key in your table? and What would the value of id in the 3rd record be? — QuakeCore
– QuakeCore, Commented May 16, 2018 at 8:04
Yes id is the primary key. The value of the the Nth record would be the (Nth-1) value in the vector (assuming the vector index starts from 0) — cateof
– cateof, Commented May 16, 2018 at 8:06
I see duplicate entries in the vector, and since the id is a primary key the update cannot be done. — QuakeCore
– QuakeCore, Commented May 16, 2018 at 8:08
Please show the expected end result donc use "etc" to describe it, as in your case there could be many solutions. — Nerevar
– Nerevar, Commented May 16, 2018 at 8:11

Nerevar · Accepted Answer · 2018-05-16 11:32:10Z

3

I suggest you transform your vector into a table :

CREATE TEMPORARY TABLE Vectors(
ID int NOT NULL AUTO_INCREMENT,
Value int not null)

INSERT INTO Vectors VALUES(10),(11),(10) ...

UPDATE MyTable AS T 
INNER JOIN Vectors AS V ON V.ID=T.ID 
SET T.Value = V.Value 
WHERE T.Value = -1

DROP TEMPORARY TABLE Vectors

edited May 16, 2018 at 11:32

answered May 16, 2018 at 8:25

Nerevar

3031 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Wouter · Accepted Answer · 2018-05-16 08:17:44Z

2

You could do the following (as far as I know it is impossible to do this with an update statement):

INSERT INTO table (id,value) VALUES (1,10),(2,11),(3,10),(4,11),(5,12)
ON DUPLICATE KEY UPDATE value=VALUES(value)

What does it do is an insert statement that checks if key already exists, if so it will update the row.

answered May 16, 2018 at 8:17

Wouter

1,3633 gold badges18 silver badges36 bronze badges

Comments

Shushil Bohara · Accepted Answer · 2018-05-16 09:33:18Z

2

TRY THIS: I think we can achieve this through UNION ALL in a single query

UPDATE test t
INNER JOIN (
            SELECT 1 id, 10 val UNION ALL
            SELECT 2 id, 11 val UNION ALL
            SELECT 3 id, 10 val UNION ALL
            SELECT 4 id, 11 val UNION ALL
            SELECT 5 id, 12 val) vector ON vector.id = t.id
SET t.val = vector.val           
WHERE t.val = -1;

answered May 16, 2018 at 9:33

Shushil Bohara

5,6662 gold badges19 silver badges32 bronze badges

Collectives™ on Stack Overflow

Update multiple rows at once in MySQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related