SQL Combine SELECT and UPDATE to replace value

March 7, 2023

I’m struggling with replacing value in my wordpress database.

I want to change status from "sent" to "completed" in statuses that are related to termins from the past.

This is sql which finds what I need:

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'

I tried to replace status with such sql:

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'

but I have an error "Something is wrong in your syntax close to ‘FROM wp_postmeta pm1
INNER JOIN wp_postmeta pm2 ON pm2.post_id = pm1.post_id AN’ in line 2"

>Solution :

Try this:

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'

You don’t need ‘from’ in mysql also the set should come after your join and before your where