How do I combine two UPDATE statements in one MySQL query?

Question

Greetings,

How would one go about performing two UPDATE statements in one query, for example:

UPDATE albums SET isFeatured = '0' WHERE isFeatured = '1'

combined with

UPDATE albums SET isFeatured = '1' WHERE id = '$id'

Basically, when a new album is featured, the previously featured album is switched back to normal and the newly featured one is set to active.

Thanks!

Answer 1

Try this:

UPDATE albums SET isFeatured = IF(id!='$id', '0','1')

Answer 2

When you have to do this sort of thing it is an indicator that your data model is wrong and could do with some fixing.

So, I'd recommend to add a seperate table featured_albums (FK: int id_album) and use that to determine if the album is featured.

Your update becomes

DELETE FROM featured_album; INSERT INTO featured_album SET id_album = $id;

When selecting join the tables

SELECT album.id,
       album.name, 
       ( id_album IS NOT NULL ) AS isfeatured
FROM   album
LEFT JOIN featured_album ON id_album = album.id 

---

As requested to expand on the above basically I'm suggesting adding a table that will contain a row indicating the currently selected album. This is a 1 to 1 relationship, i.e. one record in the album table has one related record in the feature_albums table. See Types of Relationship.

You remove the isFeatured field from the album table and add a new table.

CREATE TABLE `featured_album` (
    `id_album` INTEGER NOT NULL,
    FOREIGN KEY (id_album) REFERENCES `album` (`id`)
);

The DELETE FROM .. INSERT INTO line sets the featured album by creating an entry in the table.

The SELECT statement with the LEFT JOIN will pull in the records from the album table and join those that match from the featured_album table, in our case only one record will match so as there is one field in the featured_album table it will return NULL for all records except the featured album.

So if we did

SELECT album.id, album.name, featured_album.id_album as isFeatured0
FROM   album
LEFT JOIN featured_album ON id_album = album.id 

We'd get something like the following:

+----+----------------+------------+
| id | name           | isFeatured |
+----+----------------+------------+
|  1 | Rumours        |       NULL |
|  2 | Snowblind      |       NULL |
|  3 | Telegraph road |          3 |
+----+----------------+------------+

i.e. a NULL for isFeatured or an ID.

By adding the ( id_album IS NOT NULL ) AS isfeatured and using the first query we get

+----+----------------+------------+
| id | name           | isfeatured |
+----+----------------+------------+
|  1 | Rumours        |          0 |
|  2 | Snowblind      |          0 |
|  3 | Telegraph road |          1 |
+----+----------------+------------+

i.e. 0/1 for isfeatured which makes things more readable, although if you're processing the results in PHP it won't make a difference to your code.