Table Values() Constructor for Updating Multiple R

问题:

I just moved an app from a local instance where I used Postgres to a Google Compute Engine virtual instance where I'm using Google Cloud SQL, built on MySQL.

Ever since the move, this SQL query is no longer working:

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

where value_pairs = (1,2)

Here's the exact error I'm seeing:

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]

The output seems correct ... anyone see what I'm doing wrong?

Update / Solution Should have clarified, value_pairs can grow to be multiple values, i.e. ((4,2), (6,1), (10,3), ...)

Due to the relatively simple nature of this query, I ended up going with an INSERT query using ON DUPLICATE KEY clause:

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

回答1:

You should be able to replace (VALUES "+ value_pairs +") AS up(id, photographers) with something like this:

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>

回答2:

You could create a temporary table to run your query in MySQL:

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

And then run your update using src. It's not as pretty as the anonymous temporary table that you're currently using, but it'll work.

Another approach is to use a giant case statement:

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

回答3:

Use simple update:

UPDATE events 
SET photographers = 2
WHERE id = 1;

MySql doesn't support the non-standard syntax of PostgreSql.

---

If multiple values are needed, a multitable update syntax might be used:
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]

One option is to insert values to a temporary table - as @Denis wrote - then perform an update:

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

Another option is - as @Razvan Musaloiu-E. wrote - build a dynamic query with union:

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