开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜