simple update with right join using mysql

Question

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

Answer 1

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;

Answer 2

update wp_wpfb_files set attach_id = 
   (SELECT wp_posts.ID FROM `wp_posts`
           RIGHT OUTER JOIN 
          `wp_wpfb_files` ON 
         wp_posts.post_name = wp_wpfb_files.file_display_name
   )