如何在一个 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 语句将拉入专辑表中的记录并连接从 features_album 表中匹配的记录，在我们的例子中，只有一条记录将匹配，因此在 features_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 的 NULL 或 ID.

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屋！