CODEWITHSUNDEEP
mysql
panthro
panthro

Reputation: 24061

Set all rows except 1

I have a flag in my database called published, I set this to 1 for a published row. My question is , is there a way to set all other rows to 0 and set a particular row to 1 with just one query.

At the moment im using:

$db->query("UPDATE my_table SET published = '0'");
$db->query("UPDATE my_table SET published = '1' WHERE id = '$id'");

Upvotes: 3

Views: 494

Answers (3)

Vincent Savard
Vincent Savard

Reputation: 35927

In MySQL, there's no boolean type (conditions return an integer), so this works too :

UPDATE my_table
SET published = (id = $id);

id = $id returns 0 if $id is different than id, else 1.

Upvotes: 0

Matt Busche
Matt Busche

Reputation: 14333

Use a CASE Statement

UPDATE my_table
SET published = CASE
    WHEN id = '$id' THEN 1
    ELSE 0 END

Upvotes: 1

Wrikken
Wrikken

Reputation: 70460

UPDATE my_table SET published = IF (id = $id,1,0);

Upvotes: 4

Related Questions