Replace a string in MySQL in a specific field depending on the value of another field

3 Views

How can I find and replace in MySQL depending on the values of another field? For example,

I want to replace all of the “0” values in the column “post_parent” with “126810”…but only if those entries also have a value of “topic” in the “post_type” field.

Read More

I thought it would be something like this but it doesn’t seem to work, syntax issues and all: 

update wp_posts set post_parent = replace(post_parent,`0`,`126810`) 
WHERE `post_type` LIKE 'topic');

I also guess that even if I could have gotten that to work, I would have replaced all post_parent values of 10, 20, 30, etc. with 1126810, 2126810, etc.

Anyone happen to know how I could solve both of those issues?

Post Views: 3

Related posts

Leave a Reply

2 comments

  2. Perhaps a case when would help you.

    update wp_posts 
set post_parent = 
case when (`post_type` LIKE 'topic'
and post_parent = `0`)
then
replace(post_parent,`0`,`126810`)
end

    ;