Update same table with IF ELSE Condition in MySQL

Question

I am trying to update the user_status filed with condition:

update pm_users 
    set user_status = if ( 
      (select u.user_status from pm_users u where u.user_id = 3
      ) = '1', '0', '1' )
    where user_id = 3

means if user_status = 1 then update the status with 0 and if user status is 0 then with 1.

I am getting the error: You can't specify target table 'pm_users' for update in FROM clause

I think it means i can't use this query like above for the same table? I am not sure.
Please help me to move on the right way, and make me correct.

Are you meant to update the status of user_id = 3 based on the status of user_id = 2? — Adriaan Stander
– Adriaan Stander, Commented Aug 12, 2013 at 5:26
@astander sorry for the little typo mistake, both the user id is same, i have updated my question. — jogesh_pi
– jogesh_pi, Commented Aug 12, 2013 at 5:30
As far as the user_id condition is same you can try Case When user_status = 0 Then 1 else 0 end — Nilesh
– Nilesh, Commented Aug 12, 2013 at 5:33

Vikram · Accepted Answer · 2013-08-12 05:32:39Z

11

try the following

update pm_users 
    set user_status = case when user_status = 0 then 1 else 0 end
    where user_id = 3

answered Aug 12, 2013 at 5:32

Vikram

8,32335 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adriaan Stander · Accepted Answer · 2013-08-12 05:40:54Z

0

Rather then maybe try CASE

update pm_users 
    set user_status = CASE WHEN user_status = '1' THEN '0' ELSE '1' END
WHERE user_id = 3

SQL Fiddle DEMO

edited Aug 12, 2013 at 5:40

answered Aug 12, 2013 at 5:33

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

1 Comment

jogesh_pi Over a year ago

OHH, you mean i don't have to user select query for the same table

Collectives™ on Stack Overflow

Update same table with IF ELSE Condition in MySQL

2 Answers 2

Comments

SQL Fiddle DEMO

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related