CODEWITHSUNDEEP
mysqlsqlperformance
Tarek
Tarek

Reputation: 3798

Multi sql updates in a single database query

Is it possible in Mysql to turn this query into 1 query ?

UPDATE users SET users.title =1 WHERE  users.oldId =  'adab01' LIMIT 1 ; 
UPDATE users SET users.title =2 WHERE  users.oldId =  'agok01' LIMIT 1 ; 
UPDATE users SET users.title =1 WHERE  users.oldId =  'alla01' LIMIT 1 ; 
UPDATE users SET users.title =2 WHERE  users.oldId =  'allm01' LIMIT 1 ;

Something similar to this in SELECT :

INSERT INTO users (first_name,last_name) VALUES("John", "Doe"),("Jane", "Doe")

Is this doable in MySql ?

Just so you know what i was trying to do :

I was reading this article , Jump to (Avoid doing SQL queries within a loop)

http://code.google.com/speed/articles/optimizing-php.html

Upvotes: 0

Views: 84

Answers (1)

Adam Robinson
Adam Robinson

Reputation: 185643

You'd need to use a CASE statement to do this, and it won't be terribly pretty:

UPDATE USER SET USER.title = (CASE USER.oldId
                              WHEN 'adab01' THEN 1
                              WHEN 'agok01' THEN 2
                              WHEN 'alla01' THEN 1
                              WHEN 'allm01' THEN 2 
                              ELSE USER.oldID end)
WHERE USER.oldId in
(
    'adab01',
    'agok01',
    'alla01',
    'allm01'
)

Note that the WHERE is optional, as without it the data shouldn't be changed unnecessarily, but it will also "touch" every row in the table rather than only those that need updating.

Upvotes: 2

Related Questions