Search code examples
mysqlsqljoinsql-updateright-join

simple update with right join using mysql

I have two tables that each include a identical value (in most cases)

I am able to get the row ID from table 1, where table 1 value = table 2 value

SELECT wp_posts.ID
FROM `wp_posts`
RIGHT OUTER JOIN `wp_wpfb_files` ON wp_posts.post_name = wp_wpfb_files.file_display_name)

Now I want to update table 2 and set the column attach_id to equal the post ID from table 1 where they share the same value as per the results of the join. I'm having trouble wrapping my head around this.

Thanks in advance for any help

Solution

  • Try this: 

    UPDATE `wp_wpfb_files` wf 
LEFT JOIN `wp_posts` wp ON wf.file_display_name = wp.post_name 
SET wf.attach_id = wp.ID;