CODEWITHSUNDEEP
mysqldatabase
cateof
cateof

Reputation: 6758

Update multiple rows at once in MySQL

I have a vector of values

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

and an initialized database table

enter image description here

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:

enter image description here

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

Upvotes: 2

Views: 187

Answers (3)

Nerevar
Nerevar

Reputation: 303

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

Upvotes: 3

Shushil Bohara
Shushil Bohara

Reputation: 5656

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;

Upvotes: 2

Wouter
Wouter

Reputation: 1353

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.

Upvotes: 2

Related Questions