CODEWITHSUNDEEP
mysqlsqljoinsql-updateright-join
psorensen
psorensen

Reputation: 827

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

Upvotes: 2

Views: 5651

Answers (2)

Saharsh Shah
Saharsh Shah

Reputation: 29051

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;

Upvotes: 4

Aravind Kishore
Aravind Kishore

Reputation: 67

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
   )

Upvotes: 1

Related Questions