Question 1896 in Wordpress Question posted in
The official Wordpress documentation can be found here.

SQL Combine SELECT and UPDATE to replace value – WordPress

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"

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    You are using SQL Server’s syntax which is not valid in MySql.

    This is the correct syntax:

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

    But, if wp_postmeta.meta_value contains only the string 'sent' and you want to change it to 'completed' then this would be more appropriate:

    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';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search