Transform two queries into one update query

Question

I would like to know if it's possible to combine these two query.

My table look like this :

------------------------------------
| id | server  | domain   | status |
------------------------------------
| 1  | serv1   | serv1.az | 0      |
| 2  | serv2   | serv2.lo | 0      |
| 3  | serv2   | servNEW  | 1      |
| 4  | serv3   | serv.as  | 0      |
------------------------------------

I made these two queries to update the column :

UPDATE `table` SET `server` = ? WHERE `server` = ?
UPDATE `table` SET `domain` = ? WHERE `domain` = ?

E.g :

UPDATE `table` SET `server` = `serv1_NEW` WHERE `server` = `serv1`
UPDATE `table` SET `domain` = `serv1NEW` WHERE `domain` = `serv1.az`

EDIT:

Corrected syntax error

UPDATE `table` SET `server` = `serv1_NEW` WHERE `server` = 'serv1'
UPDATE `table` SET `domain` = `serv1NEW` WHERE `domain` = 'serv1.az'

Multiple fields can be updated by using a comma to divide them. *UPDATE `table` SET `server` = 'serv1_NEW', `domain` = 'serv1NEW' WHERE `domain` = 'serv1.az';* — LegendaryJLD, Dec 04 '18 at 15:13
Also, here's a related answer: https://stackoverflow.com/questions/3432/multiple-updates-in-mysql but I want to point out that dynamic updating multiple rows with different values is usually more trouble than just running two queries back to back, one for each row that needs updating — LegendaryJLD, Dec 04 '18 at 15:15

Maksym Fedorov · Accepted Answer · 2018-12-04T16:04:15.177

1

In the simple case, you can use CASE statement. For example:

UPDATE `table` SET 
`server`= CASE
    WHEN `server` = 'serv1' THEN `serv1_NEW`
    ELSE `server`
END, 
`domain`= CASE
    WHEN `domain` = 'serv1.az' THEN `serv1NEW`
    ELSE `domain`
END
WHERE `server` = 'serv1' OR `domain` = 'serv1.az'

But two separate queries are more clear solution for your task

edited Dec 04 '18 at 16:04

answered Dec 04 '18 at 15:16

Maksym Fedorov

6,383
2
11
31

Alright thank you to point it out. I will stay with these two queries – executable Dec 04 '18 at 15:40
@executable still this answer contain syntax errors because of mixing up backticks where single/double quotes should have been used `serv1` and `serv1.az` are string values which should have single/double quotes – Raymond Nijland Dec 04 '18 at 15:48
Yes, my mistake while writing the question. I'll correct it. – executable Dec 04 '18 at 15:49
Thanks for the comment! I didn't look it. I fixed the answer. – Maksym Fedorov Dec 04 '18 at 16:07

Transform two queries into one update query

1 Answers1