Update status of record: one record to be active and all other will be inactive
I have a enum('Y','N')
field in db table with many records hold the status of record active(Y
) or inactive(N
) .
Only one record can be active at a time. An interface is given to show all the records with their status if it is active then it is showing word Active
if it is inactive a submit button provided to make it active with each record.
Right now I am doing with two update commands
update table_name set active="Y" where id=$id
update开发者_StackOverflow中文版 table_name set active="N" where id != $id
My question is:-
Really Do I needs two update command to execute or Is their any update command to update them in once?
You could do
UPDATE table_name
SET active =
CASE WHEN id = $id THEN "Y"
ELSE "N"
END
Confirm the syntax though - I didn't actually try the code.
UPDATE table_name
SET active = IF(id = $id, 'Y', 'N')
WHERE (id = $id) OR (active = 'Y')
This will use the indexes on id
and active
.
精彩评论