CODEWITHSUNDEEP
mysql
Chinmay235
Chinmay235

Reputation: 3414

Nested Updated MySQL Query

Table - galleries

+----+----------+------------+----------+
| id | user_id  | is_primary |  photo   |
+----+----------+------------+----------+
|  1 | 1        | 1          | img1.jpg |
|  2 | 2        | 1          | img2.jpg |
|  3 | 1        | 0          | img3.jpg |
|  4 | 1        | 0          | img4.jpg |
|  5 | 1        | 0          | img5.jpg |
|  6 | 3        | 1          | img6.jpg |
|  7 | 2        | 0          | img7.jpg |
+----+----------+------------+----------+

UPDATE galleries set is_primary=0 WHERE user_id=1
UPDATE galleries set is_primary=1 WHERE id=4

There have a column name is_primary i need to set only 1 rows is_primary=1 which user id 1 But there have already one row which is_primary = 1 and user_id=1

I want to updated is_primary=1 which id=4 before updated I need to set all is_primary=0 which user_id=1.

I don't want to write 2 times query for updated.

How to write nested query for update my record?

Upvotes: 1

Views: 37

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44874

You can use case-when something as

update galleries 
 set is_primary = 
 case 
   when id=4 then 1 else 0 
 end 
 where user_id = 1 ;

Upvotes: 1

Related Questions