MySQL: Updating all rows setting a field to 0, but setting one row's field to 1

Question

Is there an efficient way to update a selection of rows' field to 0, but set One of these rows to 1 based on an ID.

Basically, I have multiple objects in a database, and I want to toggle between which one is "inuse", so the query will set one of the rows (by id) to inuse=1 and the others to inuse=0.

Thanks :)

Answer 1

Sure

UPDATE table SET inuse=IF(id=ABCD, 1, 0)

would set the inuse field to 1 if id is ABCD and 0 otherwise.

Answer 2

UPDATE myTable
SET Field = 0
WHERE FieldID <> [WhateverID]

UPDATE myTable 
SET Field = 1
WHERE FieldId = [WhateverID]

Answer 3

UPDATE `table`
SET `inuse` = (`id` = 23)

Answer 4

Try

update tbl set inuse = if(test, 1, 0);

or shorter

update tbl set inuse = test;

for example

update tbl set inuse = name = 'foo';

Answer 5

UPDATE  table
SET     inuse = (id = @id)
WHERE   id = @id
        OR inuse

This will update only relevant rows.

Answer 6

If you are after to set a flag, so no other part of the code uses the same object at the same time, it's better if the calling code sets inuse=1 and resets it when done. Otherwise you will end up one thread to mark an object (row) as inuse, and then if another thread needs another object, it will reset the first one, while still in use.

If this is not the case, and you just want be able to set inuse for one, and reset all others, you can use:

UPDATE myTable
SET InUse = CASE
   WHEN myTable.id = @id THEN 1
   ELSE 0
END

Answer 7

if your database uses transactions, this is the best way to do it:

update myTable set inuse = 0;
update myTable set inuse = 1 where id = ?;

if you are not using transactions, then the other answer using CASE is the best option since it's portable. but it will require more CPU time than the two UPDATE statements.