用于更新多行的表 Values() 构造函数

Question

我刚刚将一个应用程序从我使用Postgres的本地实例移动到一个Google Compute Engine虚拟实例，在那里我使用基于MySQL构建的Google Cloud SQL。

自从移动以来，此 SQL 查询不再工作：

"UPDATE events e SET e.photographers = up.photographers FROM (VALUES "+ value_pairs +") AS up(id, photographers) WHERE up.id = e.id"

其中 value_pairs = （1，2）

这是我看到的确切错误：

error running query { [Error: ER_PARSE_ERROR: 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 'FROM (VALUES (1,2)) AS up(id, photographers) WHERE up.id = e.id' at line 1]

输出似乎是正确的...有人看到我做错了什么吗？

更新/解决方案应该澄清一下，value_pairs可以增长为多个值，即（（4，2），（6，1），（10，3），...

由于此查询的性质相对简单，我最终使用 ON DUPLICATE KEY 子句进行了INSERT查询：

("INSERT INTO events (id,photographers) VALUES "+ value_pairs + "ON DUPLICATE KEY UPDATE photographers=VALUES(photographers)"

Answer 1

您应该能够将(VALUES "+ value_pairs +") AS up(id, photographers)替换为以下内容：

mysql> (SELECT 1 AS photographers, 2 AS id) UNION (SELECT 3, 4) UNION (SELECT 5, 6);
+---------------+----+
| photographers | id |
+---------------+----+
|             1 |  2 | 
|             3 |  4 | 
|             5 |  6 | 
+---------------+----+
3 rows in set (0.00 sec)
mysql>

Answer 2

您可以创建一个临时表来在 MySQL 中运行查询：

create temporary table src ...
insert into src values ...

然后使用 src 运行更新。它不像您当前使用的匿名临时表那么漂亮，但它可以工作。

另一种方法是使用一个巨大的案例语句：

update events
set photographers = case id
                    when 1 then 2
                    ...
                    end
where id in (1, ...)

Answer 3

使用简单的更新：

UPDATE events 
SET photographers = 2
WHERE id = 1;

MySql 不支持 PostgreSQL 的非标准语法。

---

如果需要多个值，可以使用多表更新语法：
http://dev.mysql.com/doc/refman/5.7/en/update.html

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]

一种选择是将值插入临时表 - 如@Denis所写 - 然后执行更新：

UPDATE table t, temporary_table tmp
SET t.photographers = tmp.photographers
WHERE t.id = tmp.id

另一种选择是 - 正如 Musaloiu-E 所写@Razvan - 使用联合构建动态查询：

UPDATE table t, (
   SELECT 1 AS id, 2 AS photographers
   UNION
   SELECT 5, 7
   UNION
   SELECT 15, 67
   UNION
   .....
   .....
   .....
   .....
   UNION
   SELECT 234, 567 
) AS tmp
SET t.photographers = tmp.photographers
WHERE t.id = tmp.id