SQL: join two tables (LEFT OUTER JOIN) then update values

mysql, sql, sql-update

Solution

Try this way, if you use SQL Server

UPDATE WP
SET WP.post_date = REPLACE(WPM.post_date, 'X', 'Y') 
FROM wp_posts WP 
   LEFT OUTER JOIN wp_postmeta WPM ON WP.post_id = WPM.post_id
WHERE WP.meta_value = 'Z';

UPDATE:

If you're using MySQL. Here's Update for MySQL

UPDATE wp_posts WP 
   LEFT JOIN wp_postmeta WPM ON WP.post_id = WPM.post_id
SET WP.post_date = REPLACE(WPM.post_date, 'X', 'Y') 
WHERE meta_value = 'Z';

Problem

Im trying to update a value in one column based on a value in another table. I can do this if the columns are all in the same table like this: ``` UPDATE wp_posts SET post_date = REPLACE (post_date, 'X', 'Y') WHERE meta_value = 'Z'; ``` But I need to join two tables first before I can update: ``` SELECT * FROM wp_posts LEFT OUTER JOIN wp_postmeta ON post_id = post_id ``` How can I update based on the two tables I have joined?

Original source