mysql update with several joins

Question

I would like to update with 2 join.... but :

 UPDATE glpi.glpi_users 
FROM
    glpi.glpi_groups_users
    INNER JOIN glpi.glpi_groups ON glpi.glpi_groups_users.groups_id = glpi.glpi_groups.id
    INNER JOIN glpi.glpi_users ON glpi.glpi_users.id = glpi.glpi_groups_users.users_id 
    SET glpi.glpi_users.id = 2 
WHERE
    glpi.glpi_groups.`name` LIKE 'technique'

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM glpi.glpi_groups_users INNER JOIN glpi.glpi_groups ON glpi.glpi_groups_us' at line 2 Time: 0s

thanks for your help

no `FROM` is needed, see: https://stackoverflow.com/questions/15037883/mysql-update-syntax-with-multiple-tables-using-where-clause and https://dev.mysql.com/doc/refman/8.0/en/update.html — Luuk, Apr 11 '20 at 17:34

score 0 · Answer 1 · answered Apr 11 '20 at 17:32

I think that the syntax you want is:

UPDATE glpi.glpi_users u
INNER JOIN glpi.glpi_groups_users gu ON gu.users_id = u.id
INNER JOIN glpi.glpi_groups g ON g.id = gu.groups_id
SET u.id = 2 
WHERE g.name = 'technique'

That is: MySQL update/join syntax doesn't have a FROM clause - it looks like UPDATE ... JOIN ... SET ... WHERE ....

Notes:

since no wildcards do appear in the right side operand, name LIKE 'technique' is equivalent to name = 'technique'
table aliases make the query easier to write and read

mysql update with several joins

1 Answers1