SQL:连接两个表(LEFT OUTER JOIN)然后更新值 [英] SQL: join two tables (LEFT OUTER JOIN) then update values

问题描述

我正在尝试根据另一表中的值更新一列中的值.

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?

推荐答案

试试这个，如果你用的是SQL Server

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';

更新:

如果您使用的是 MySQL.这是 MySQL

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';

这篇关于SQL:连接两个表(LEFT OUTER JOIN)然后更新值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！