CODEWITHSUNDEEP
mysqlsql
executable
executable

Reputation: 3600

Transform two queries into one update query

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'

Upvotes: 1

Views: 42

Answers (1)

Maksym Fedorov
Maksym Fedorov

Reputation: 6456

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

Upvotes: 1

Related Questions