MYSQL - UPDATE/INNER JOIN

Question

I'm trying to make an UPDATE statement with an INNER JOIN in (MY)SQL. I already took examples and edited the table names and table columns to mine. But my MYSQL still keep giving me an error (#1064).

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 favorites INNER JOIN images ON favorites.image_id = images.id W' at line 5

SQL

UPDATE
  favorites
SET
  favorites.archive = 1
FROM
  favorites
INNER JOIN
  images
ON
  favorites.image_id = images.id
WHERE
  favorites.user_id = '1'

Thanks in advance in helping me out on this one.

Kind regards,

Answer 1

Update syntax is different in MySQL. You cannot use From clause inside an Update statement.

It is like:

Update (all the tables with join relationships if any) 
Set (field = value/expression) 
Where (all the conditions)

Try the following query instead:

UPDATE
  favorites 
INNER JOIN
  images
ON
  favorites.image_id = images.id
SET
  favorites.archive = 1
WHERE
  favorites.user_id = '1'

---

Full syntax is:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

Answer 2

The following should to the trick for you.

UPDATE favorites 
INNER JOIN images ON favorites.image_id= images.id
SET favorites.archive = 1 
WHERE favorites.user_id = '1';