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

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, t开发者_如何学Gohe previously featured album is switched back to normal and the newly featured one is set to active.

Thanks!

Try this:

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

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.

You can use CASE WHEN statement and remember to set original value where necessary (ELSE clause below) and order CASE conditions as required (in statement below isFeatured will be 0 if row having requested id also has isFeatured = 1, to change it swap WHEN clauses).

UPDATE albums 
SET isFeatured = CASE 
  WHEN isFeatured = '1' THEN '0' 
  WHEN id = '$id' THEN '1'
  ELSE isFeatured
END

You just can't. You can only select one group of records that should be updated and can then only perform one operation on all of them. It's not possible to do

 UPDATE x SET col1 = 1 WHERE col1 = 0 AND col1 = 0 WHERE col1 = 1;

Be careful when using functions to work around this, as they need to be evaluated for every row and this can become really expensive.

MySQL is unable to use the index when it is inside an if function: You need an index on the function which is not possible in MySQL.

see also: How does one create an index on the date part of DATETIME field in MySql

I am using the employee test database http://dev.mysql.com/doc/employee/en/employee.html

mysql> describe employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int(11)       | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
|   300024 |
+----------+
1 row in set (0.37 sec)

Set all genders to male so it mimics the question.

mysql> update employees set gender = 'M';
Query OK, 1 row affected (9.11 sec)
Rows matched: 300024  Changed: 1  Warnings: 0

mysql>  select emp_no, gender from employees order by emp_no limit 2;
+--------+--------+
| emp_no | gender |
+--------+--------+
|  10001 | M      |
|  10002 | M      |
+--------+--------+
2 rows in set (0.00 sec)

Set one employee to female. (Notice it uses the index and is almost instant.)

mysql> update employees set gender = 'F' where emp_no = 10001;
Query OK, 1 row affected (0.14 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Now we use the suggested answer. (Notice it does not use the index and touches every row.)

mysql> update employees set gender = if(emp_no=10002, 'F', 'M');
Query OK, 2 rows affected (10.67 sec)
Rows matched: 300024  Changed: 2  Warnings: 0

Will an index help?

> mysql> create index employees_gender_idx  on employees(gender);
Query OK, 300024 rows affected (21.61 sec)
Records: 300024  Duplicates: 0  Warnings: 0

> mysql> update employees set gender = if(emp_no=10001, 'F', 'M');
Query OK, 2 rows affected (9.02 sec)
Rows matched: 300024  Changed: 2  Warnings: 0

Nope.

It was also said that MySQL is only going to look at the rows that need to be changed.

mysql> update employees set gender = 'M';
Query OK, 1 row affected (8.78 sec)
Rows matched: 300024  Changed: 1  Warnings: 0

Guess not. What if use a WHERE clause?

mysql> update employees set gender = 'M' where gender ='F';
Query OK, 0 rows affected (0.03 sec)
Rows matched: 0  Changed: 0  Warnings: 0

Gee that fast, now it used the index.

Mysql has no idea what the IF function will return and must do a full table scan. Notice that WHERE really does mean where and SET really does mean set. You can't expect the DB to just arrange all your clauses to get good performance.

The correct solution is to issue two updates (which if use indexes will be almost instant.)

Notice, it was said elsewhere that MySQL will magically know only update the rows it needs to change.

Adding an alternate method to the excellent answer provided by @too where instead of CASE IF statement is used -

UPDATE album
    -> SET isFeatured = IF (
    -> isFeatured = '1', '0', IF (
    -> id = '$id', '1', isFeatured
    -> ));

I don't think you can, or at least not in a neat or practical way.

If you're wanting to do one call from php/whatever then you can seperate them with semicolons thus:

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