在单个 MySQL 查询中更新多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18802671/
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
Update multiple rows in a single MySQL query
提问by user2618929
I am trying to run this:
我正在尝试运行这个：
UPDATE test
SET col2=1 WHERE col1='test1',
SET col2=3 WHERE col1='test2';
The error I am getting:
我得到的错误：
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
My table:
我的表：
CREATE TABLE `test` (
`col1` varchar(30) NOT NULL,
`col2` int(5) DEFAULT NULL,
PRIMARY KEY (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
It's something about ,at the end of the first row. When I changed it to ;, it didn't recognize col2. How can I do this in one query?
这是关于,第一行末尾的内容。当我将其更改为 时;，它无法识别 col2。我怎样才能在一个查询中做到这一点？
回答by user3435275
This is most clear way
这是最清楚的方式
UPDATE test
SET col2 = CASE col1
WHEN 'test1' THEN 1
WHEN 'test2' THEN 3
WHEN 'test3' THEN 5
END,
colx = CASE col1
WHEN 'test1' THEN 'xx'
WHEN 'test2' THEN 'yy'
WHEN 'test3' THEN 'zz'
END
WHERE col1 IN ('test1','test2','test3')
回答by Ruud H.G. van Tol
Consider using INSERT-ODKU (ON DUPLICATE KEY UPDATE), because that supports to update multiple rows.
考虑使用 INSERT-ODKU (ON DUPLICATE KEY UPDATE)，因为它支持更新多行。
Make sure that the values of all PK columns are in the VALUES().
确保所有 PK 列的值都在 VALUES() 中。
Where feasible, generate the SQL with data from a slave.
在可行的情况下，使用来自从站的数据生成 SQL。
回答by John Woo
you can use CASEon this
你可以用CASE在这个
UPDATE test
SET col2 = CASE WHEN col1 = 'test1' THEN 1 ELSE 3 END
WHERE col1 IN ('test1', 'test2')
or IF(for MySQLonly)
或IF（MySQL仅限）
UPDATE test
SET col2 = IF(col1 = 'test1', 1, 3)
WHERE col1 IN ('test1', 'test2')
回答by mnagel
alternatively when the construct with casesgets too unreadable, you could/should start a transaction and just do the updates sequentially.
或者，当 with 的构造cases变得太不可读时，您可以/应该启动一个事务并按顺序进行更新。
this usually results in more straightforward sql, except if the first statements creates rows that then are matched by the second statement when they should not. however this is not the case in your example.
这通常会导致更直接的 sql，除非第一个语句创建的行然后被第二个语句匹配，而它们不应该匹配。但是，在您的示例中情况并非如此。
回答by JDrake
This is how I did it:
我是这样做的：
UPDATE col1(static value), col2(static value), and col3(different values) WHERE col4has different values AND col5is static.
UPDATE col1（静态值）、col2（静态值）、和col3（不同的值）WHERE col4有不同的值AND col5是静态的。
$someArray = ["a","b","c"];
$anotherArray = [1,2,3];
$sql = "UPDATE table SET col1 = '$staticValue1', col2 = '$staticValue2', col3 = CASE col4";
$sqlEnd = " END WHERE col4 IN (";
$seperator = ",";
for ( $c = 0; $c < count($someArray); $c++ ) {
$sql .= " WHEN " . "'" . $someArray[$c] . "'" . " THEN " . $anotherArray[$c];
if ( $c === count($someArray) - 1 ) {
$separator = ") AND col5 = '$staticValue5'";
}
$sqlEnd .= "'" . $someArray[$c] . "'" . $seperator;
}
$sql .= $sqlEnd;
$retval = mysqli_query( $conn, $sql);
if(! $retval ) {
/* handle error here */
}
And the output string for MySql query would be something like this:
MySql 查询的输出字符串将是这样的：
UPDATE table SET col1 = '1', col2 = '2', col3 = CASE col4 WHEN 'a' THEN 1 WHEN 'b' THEN 2 WHEN 'c' THEN 3 END WHERE col4 IN ('a','b','c') AND col5 = 'col5'
回答by Taron
UPDATE `wp_partners_tarif` SET `name`="name1",`cena`="1",`comisiya`="11" WHERE `id`=2;
UPDATE `wp_partners_tarif` SET `name`="name2",`cena`="2",`comisiya`="22" WHERE `id`=3;
UPDATE `wp_partners_tarif` SET `name`="name2",`cena`="3",`comisiya`="33" WHERE `id`=4
