CODEWITHSUNDEEP
mysqlsql
Alex
Alex

Reputation: 3079

INNER JOIN on a subquery using UNION

I have an SQL query to update the rotational offset of a photo. The query should add 1 to the value, unless the value is 3, in which case it should set it back to 0 (as in, 4 lots of 90 degrees rotation is the same as no rotation at all).

I'm not sure if what's below is the most efficient way to do it, and I'd appreciate suggestions of simpler solutions that don't require me to list out the mappings like I have.

However, my question is, why do I get a MySQL syntax error? When I use the SELECT subquery on it's own, it returns the result as expected.

UPDATE `photos` p SET p.rotational_offset=map.new_value INNER JOIN (
SELECT 0 AS rotational_offset, 3 AS new_value
UNION SELECT 1 , 0
UNION SELECT 2 , 1
UNION SELECT 3 , 2
) map ON p.rotational_offset=map.rotational_offset WHERE p.photo_id="22";

Upvotes: 1

Views: 704

Answers (1)

Mark Byers
Mark Byers

Reputation: 838076

First, a much simpler solution is to use the MOD operator (%):

UPDATE photos
SET rotational_offset = (rotational_offset + 1) % 4
WHERE photo_id = '22'

See it working online: sqlfiddle

And now to actually answer your question... You get an error because you aren't using the correct syntax for a multiple table update. The table references come first, before the SET clause. The correct syntax is as follows:

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

Your statement doesn't work because you put the inner join after the SET clause. A corrected version is as follows:

UPDATE photos AS p
INNER JOIN
(
    SELECT 0 AS rotational_offset, 3 AS new_value
    UNION ALL SELECT 1 , 0
    UNION ALL SELECT 2 , 1
    UNION ALL SELECT 3 , 2
) AS map
ON p.rotational_offset = map.rotational_offset 
SET p.rotational_offset=map.new_value
WHERE photo_id = '22'

Upvotes: 4

Related Questions