mysql SQL合并SELECT和UPDATE来替换值

zkure5ic 于 2023-03-11 发布在 Mysql

关注(0)|答案(2)|浏览(138)

我正在努力替换我的wordpress数据库中的值。
我想在与过去的终端相关的状态中将状态从“已发送”更改为“已完成”。
这是sql，它会找到我需要的东西：

SELECT pm1.meta_value AS Status, pm4.meta_value AS TerminStart
FROM wp_postmeta pm1
INNER JOIN wp_postmeta pm2 ON pm2.post_id = pm1.post_id AND pm1.meta_key = 'participant_statuses'
INNER JOIN wp_postmeta pm3 ON pm3.meta_value = pm2.meta_value AND pm2.meta_key = 'enroll_term_id'
INNER JOIN wp_postmeta pm4 ON pm4.post_id = pm3.post_id AND pm3.meta_key = 'term_id'
WHERE pm4.meta_key = 'start_date' AND pm1.meta_value REGEXP '\"sent\"' AND pm4.meta_value < '2023-03-07 00:00:00'

我尝试用这样的sql替换status：

UPDATE pm1 SET pm1.meta_value = REPLACE(pm1.meta_value, 'sent', 'completed')
FROM wp_postmeta pm1
INNER JOIN wp_postmeta pm2 ON pm2.post_id = pm1.post_id AND pm1.meta_key = 'participant_statuses'
INNER JOIN wp_postmeta pm3 ON pm3.meta_value = pm2.meta_value AND pm2.meta_key = 'enroll_term_id'
INNER JOIN wp_postmeta pm4 ON pm4.post_id = pm3.post_id AND pm3.meta_key = 'term_id'
WHERE pm4.meta_key = 'start_date' AND pm1.meta_value REGEXP '\"sent\"' AND pm4.meta_value < '2023-03-07 00:00:00'

但是我有一个错误“在第2行中靠近'FROM wp_postmeta pm1 INNER JOIN wp_postmeta pm2 ONpm2.post_id =pm1.post_id AN'的语法中有错误”

mysql

来源：https://stackoverflow.com/questions/75664627/sql-combine-select-and-update-to-replace-value

2条答案

按热度按时间

carvr3hs1#

试试这个：

UPDATE wp_postmeta pm1
INNER JOIN wp_postmeta pm2 ON pm2.post_id = pm1.post_id AND pm1.meta_key = 'participant_statuses'
INNER JOIN wp_postmeta pm3 ON pm3.meta_value = pm2.meta_value AND pm2.meta_key = 'enroll_term_id'
INNER JOIN wp_postmeta pm4 ON pm4.post_id = pm3.post_id AND pm3.meta_key = 'term_id'
SET pm1.meta_value = REPLACE(pm1.meta_value, 'sent', 'completed')
WHERE pm4.meta_key = 'start_date' AND pm1.meta_value REGEXP '\"sent\"' AND pm4.meta_value < '2023-03-07 00:00:00'

在mysql中你不需要“from”，而且set应该在你的join之后和where之前

赞(0）回复(0）举报 2023-03-11

z31licg02#

您使用的SQL Server语法在MySql中无效。
以下是正确的语法：

UPDATE wp_postmeta pm1 
INNER JOIN wp_postmeta pm2 ON pm2.post_id = pm1.post_id AND pm1.meta_key = 'participant_statuses'
INNER JOIN wp_postmeta pm3 ON pm3.meta_value = pm2.meta_value AND pm2.meta_key = 'enroll_term_id'
INNER JOIN wp_postmeta pm4 ON pm4.post_id = pm3.post_id AND pm3.meta_key = 'term_id'
SET pm1.meta_value = REPLACE(pm1.meta_value, 'sent', 'completed')
WHERE pm4.meta_key = 'start_date' AND pm1.meta_value REGEXP '\"sent\"' AND pm4.meta_value < '2023-03-07 00:00:00';

但是，如果wp_postmeta.meta_value只包含字符串'sent'，并且您希望将其更改为'completed'，则以下语句会更合适：

UPDATE wp_postmeta pm1 
INNER JOIN wp_postmeta pm2 ON pm2.post_id = pm1.post_id AND pm1.meta_key = 'participant_statuses'
INNER JOIN wp_postmeta pm3 ON pm3.meta_value = pm2.meta_value AND pm2.meta_key = 'enroll_term_id'
INNER JOIN wp_postmeta pm4 ON pm4.post_id = pm3.post_id AND pm3.meta_key = 'term_id'
SET pm1.meta_value = 'completed'
WHERE pm1.meta_value = 'sent' AND pm4.meta_key = 'start_date' AND pm1.meta_value REGEXP '\"sent\"' AND pm4.meta_value < '2023-03-07 00:00:00';

赞(0）回复(0）举报 2023-03-11

我来回答

mysql SQL合并SELECT和UPDATE来替换值

2条答案

相关问题

热门标签

最新问答