PolyLang WP Update to replace one string to another

Question

I have to replace one string to another one in post_content column in table wp_post ( Wordpress ). My query works only then when I use SELECT but when I want to use UPDATE command with REPLACE then I have error. My query:

UPDATE wp_posts SET post_content = REPLACE(post_content, 'str1', 'str2') FROM wp_posts WHERE ID IN (
SELECT ID
FROM wp_posts 
WHERE ID IN (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(description, '"ar";i:', -1), ';',1) FROM wp_term_taxonomy WHERE taxonomy = 'post_translations'))

Mysql said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM wp_posts WHERE ID IN ( SELECT ID FROM wp_posts WHERE ID IN (SELECT S...' at line 1

Answer 1

Basically, you constuction to get the right ID was doubles and UPDAT has no fROM clause

There WHERE clause you should try first in a SELECT if you get all IDs

UPDATE wp_posts 
SET post_content = REPLACE(post_content, 'str1', 'str2') 
WHERE ID IN (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(description, '"ar";i:', -1), ';',1) FROM wp_term_taxonomy WHERE taxonomy = 'post_translations') 

• Macsurf: I did it because there was not only ID. There are also unwanted strings so in IN ID() I needed only ID. So your query it was my first option and I had to updated it to current form. Some data from ID IN: #1292 - Truncated incorrect DOUBLE value: 'a:2:{s:2:"pl"'
• nbk: can you provide some sample data,, your double SELECT IN would not yield any improvment.

Answer 2

OK I have done it. Now is everything is good. This is a valid query:

UPDATE wp_posts SET post_content=REPLACE(post_content, 'str1', 'str2') WHERE ID IN( SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(description, '"ar";i:', -1), ';',1) AS IDList FROM wp_term_taxonomy WHERE taxonomy = 'post_translations' HAVING IDList REGEXP '^-?[0-9]+$')