SQL - UPDATE 中每个 SET 命令的 WHERE 子句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1421116/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL - WHERE clause on each SET command in UPDATE?
提问by Dan
I'm trying to create an SQL query in PHP to update a table.
Is it possible to have a different WHEREclause for each affected row?
我正在尝试在 PHP 中创建一个 SQL 查询来更新表。是否可以WHERE为每个受影响的行使用不同的子句？
eg something like:
例如：
UPDATE table
SET val=X WHERE someproperty = 1,
SET val=Y WHERE someproperty = 2
etc?
等等？
Any help appreciated. Thanks
任何帮助表示赞赏。谢谢
回答by David Andres
Yes, you can with a CASE statement.
是的，您可以使用 CASE 语句。
UPDATE table
SET val = CASE someproperty
WHEN 1 THEN x
WHEN 2 THEN y
....
ELSE
val
END
Now, there is concern that one CASEstatement is less readable when compared to several UPDATEstatements. There is a valid argument here. For example, when 1000 rows are being updated, it just feels and looks better to use several UPDATEstatements rather than 1000 different conditions to a single CASE.
现在，有人担心CASE与多个UPDATE语句相比，一个语句的可读性较差。这里有一个有效的论据。例如，当更新 1000 行时，使用多个UPDATE语句而不是 1000 个不同的条件到单个CASE.
However, sometimes a CASE statement is more appropriate. If, for example, you are updating rows based on some trait, say the even or odd nature of a field's value the table, then a CASEstatement is a wonderfully concise and maintainable way to update rows in the table without having to resort to a huge number of UPDATEstatements that all share a specific type of logic. Take this for example:
但是，有时 CASE 语句更合适。例如，如果您根据某些特征更新行，例如表中字段值的偶数或奇数性质，那么CASE语句是一种非常简洁且可维护的方式来更新表中的行，而无需求助于巨大的UPDATE共享特定类型逻辑的语句数。以这个为例：
UPDATE table
SET val = CASE MOD(someproperty, 2)
WHEN 0 THEN x
WHEN 1 THEN y
END
This expression takes the modulus of someproperty and, when 0 (even), assigns value x to val and, when 1 (odd), assigns value y to val. The greater the volume of data being updated by this statement, the cleaner it is compared to doing so by multiple UPDATEstatements.
此表达式采用某个属性的模数，当为 0（偶数）时，将值 x 分配给 val，当为 1（奇数）时，将值 y 分配给 val。此语句更新的数据量越大，与多个UPDATE语句更新的数据量相比，它就越干净。
In short, CASEstatements are sometimes just as readable/maintainable as UPDATEstatements. It all depends on what you are trying to do with them.
简而言之，CASE语句有时与UPDATE语句一样可读/可维护。这一切都取决于你想用它们做什么。
EDIT: Added the ELSE clause to be extra safe. The OP may be interested in updating only specific rows so the rest should remain as they prior to the UPDATE.
编辑：添加了 ELSE 子句以提高安全性。OP 可能只对更新特定行感兴趣，因此其余行应保持更新前的状态。
EDIT: Added a scenario where the CASEstatement is a more effective approach than multiple UPDATEstatements.
编辑：添加了一个场景，其中CASE语句是比多个UPDATE语句更有效的方法。
回答by Dan Polites
You cannot have multiple WHERE clauses for any SQL statement, however you can use a CASE statement to accomplish what you are trying to do. Another option that you have is to execute multiple UPDATE statements.
任何 SQL 语句都不能有多个 WHERE 子句，但是您可以使用 CASE 语句来完成您要执行的操作。另一个选项是执行多个 UPDATE 语句。
Here is a sample using the CASE statement:
以下是使用 CASE 语句的示例：
UPDATE table
SET val = (
CASE someproperty
WHEN 1 THEN X
WHEN 2 THEN Y
ELSE val
END
);
Here is a sample using multiple UPDATE statements:
这是使用多个 UPDATE 语句的示例：
UPDATE table SET val=X WHERE someproperty = 1;
UPDATE table SET val=Y WHERE someproperty = 2;
回答by Kieveli
Nope. Make it two updates:
不。进行两次更新：
UPDATE table SET val=X WHERE someproperty = 1;
UPDATE table SET val=Y WHERE someproperty = 2;
On second thought, you could use sub-queries or the case statement...
再想一想，您可以使用子查询或 case 语句...
UPDATE table SET val= ( case when someproperty = 1 then X when someproperty = 2 then Y else val END )
You may need to make that a sub query like this:
您可能需要像这样创建一个子查询：
UPDATE table t1 SET val = ( select CASE when someproperty = 1 then X when someproperty = 2 then Y ELSE val END from table t2 where t1.primarykey = t2.primary key )
回答by Ertugrul Kara
UPDATE TABLE
SET VAL CASE SOMEPROPERTY WHEN 1 THEN X WHEN 2 THEN Y END
回答by culebrón
A compact and easily scaleable way:
一种紧凑且易于扩展的方式：
UPDATE table1 SET val=ELT(FIND_IN_SET(someproperty, '1, 2'), X, Y);
make the query this way:
以这种方式进行查询：
$condition = array(1, 2);
$newvals = array('X', 'Y');
$query = "UPDATE table1 SET val=ELT(FIND_IN_SET(someproperty, '". implode(',', $condition). "', ". implode(', ', $newvals). ")";
Use prepare_query to avoid SQL syntax errors if you deal with string values.
如果您处理字符串值，请使用 prepare_query 来避免 SQL 语法错误。
