CODEWITHSUNDEEP
mysqlsql
cphill
cphill

Reputation: 5924

SQL - Update Junction Table Based on Values in User Table

I have a new migration that creates a many-to-many relationship between a user and organization through a junction table called organization_member. Since I am trying to migrate the current values of the organization_id and user_id on the user table to the junction table to represent this relationship, I thought I would try a join and update, but I can't seem to figure out how to query both tables and then take the existing columns on user and update where the relationship doesn't currently exist. Here was my original thought:

UPDATE `om`
SET `om`.`user_id` = `u`.`user_id`, `om`.`organization_id` = `u`.`organization_id`
FROM `user` AS `u`
LEFT JOIN `organization_member` AS `om`
ON `u`.`user_id` = `om`.`user_id`
WHERE `u`.`organization_id` IS NOT NULL;

but I get an error at 

'FROM `user` AS `u`
LEFT JOIN `organization_member` AS `om`
ON `u`.`user_id` = `o' at line 3

Any help would be great!

Upvotes: 0

Views: 430

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270883

In MySQL, the JOIN is part of the UPDATE. There is no separate FROM:

UPDATE `user` `u` JOIN
       `organization_member` `om`
       ON `u`.`user_id` = `om`.`user_id`
    SET `om`.`user_id` = `u`.`user_id`,
        `om`.`organization_id` = `u`.`organization_id`
    WHERE `u`.`organization_id` IS NOT NULL;

A LEFT JOIN is also inappropriate. You want to update om, so you need a matching row for the update

Upvotes: 1

Related Questions