如何在一个MySQL查询中合并两个UPDATE语句? [英] How do I combine two UPDATE statements in one MySQL query?

问题描述

问候，

如何在一个查询中执行两个UPDATE语句，例如:

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

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

与

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.

谢谢！

推荐答案

当您必须执行此类操作时，这表明您的数据模型是错误的，并且可以进行某些修复.

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.

因此，我建议添加一个单独的表featured_albums(FK:int id_album)，并使用该表来确定专辑是否带有特色.

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.

您的更新成为

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

选择联接表时

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

---

根据上述要求，基本上我建议添加一个表，该表将包含指示当前选择的专辑的行.这是一对一的关系，即专辑表中的一条记录在feature_albums表中有一条相关记录.参见关系类型.

您从相册表中删除isFeatured字段，然后添加一个新表.

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`)
);

DELETE FROM .. INSERT INTO行通过在表中创建一个条目来设置特色相册.

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

带有LEFT JOIN的SELECT语句将从专辑表中提取记录，并将那些与featured_album表中匹配的记录联接在一起，在我们的示例中，只有一条记录将匹配，因此featured_album表中将有一个字段将返回除精选专辑外，所有记录均为NULL.

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.

如果我们这样做

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

我们将得到以下内容:

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

即isFeatured或ID为NULL.

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

通过添加( id_album IS NOT NULL ) AS isfeatured并使用第一个查询，我们得到

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 |
+----+----------------+------------+

即isfeatured为0/1，这使内容更具可读性，尽管如果使用PHP处理结果，则不会对代码造成影响.

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.

这篇关于如何在一个MySQL查询中合并两个UPDATE语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！