0

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

0

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') 
2
  • 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"'
    – Macsurf
    Mar 20 at 11:29
  • can you provide some sample data,, your double SELECT IN would not yield any improvment.
    – nbk
    Mar 20 at 11:31
0

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]+$')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.