I want to update status, but using one query !!
UPDATE myTable SET `status` = 0 WHERE `name` IN ('a', 'b', 'c', 'd')
UPDATE myTable SET `status` = 1 WHERE `name` NOT IN ('a', 'b', 'c', 'd')
Asked
Active
Viewed 1,150 times
3
Talal Farhat
- 99
- 9
-
use `case when` statement – Pathik Vejani Dec 28 '15 at 08:51
-
Possible duplicate of [Update multiple rows in a single MySQL query](http://stackoverflow.com/questions/18802671/update-multiple-rows-in-a-single-mysql-query) – halfpastfour.am Dec 28 '15 at 08:52
-
Try with sql union or union all in between two queries – rohitr Dec 28 '15 at 08:52
-
try this. `UPDATE myTable SET status = IF(name IN ('a', 'b', 'c', 'd'),1,0)` – roullie Dec 28 '15 at 08:53
2 Answers
6
You can use CASE statement.
UPDATE myTable
SET status = (CASE
WHEN `name` IN ('a','b','c','d') THEN '0'
WHEN `name` NOT IN ('a','b','c','d') THEN '1'
END);
Sadikhasan
- 18,365
- 21
- 80
- 122
0
Make 'status' datatype boolean, default value 0 and for "name NOT IN ('a', 'b', 'c', 'd')"
use
UPDATE myTable SET `status` = 1 WHERE `name` NOT IN ('a', 'b', 'c', 'd');
You do not have to run two queries.
Prafulla Kumar Sahu
- 9,321
- 11
- 68
- 105
-
@TalalFarhat you are using UPDATE to update status , if status update is not possible after first how you are doing it ? or you are facing problem when you are using boolean for status ? – Prafulla Kumar Sahu Dec 28 '15 at 09:31