在单个MySQL查询中更新多行

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。如何在一个查询中执行此操作？

6 个解决方案

#1

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')

#2

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。

#3

you can use CASE on this

你可以在这上面使用CASE

UPDATE test 
SET col2 = CASE WHEN col1 = 'test1' THEN 1 ELSE 3 END 
WHERE col1 IN ('test1', 'test2')

or IF (for MySQL only)

或IF（仅限MySQL）

UPDATE test 
SET col2 = IF(col1 = 'test1', 1, 3)
WHERE col1 IN ('test1', 'test2')

#4

alternatively when the construct with cases gets too unreadable, you could/should start a transaction and just do the updates sequentially.

或者，当具有案例的构造变得太难以理解时，您可以/应该启动事务并按顺序执行更新。

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，除非第一个语句创建的行然后由第二个语句匹配，否则它们不匹配。但是在您的示例中并非如此。

#5

This is how I did it:

这就是我做的方式：

UPDATE col1 (static value), col2 (static value), and col3 (different values) WHERE col4 has different values AND col5 is static.

更新col1（静态值），col2（静态值）和col3（不同的值）WHERE col4具有不同的值，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'

#6

-3

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

#1