Mysql – PolyLang WP Update to replace one string to another

MySQLreplaceupdateWordpress

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

Best Answer

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

Related Solutions

MySQL Query – INSERT/UPDATE from Previous SELECT with Singularities

I've made a function where you just put the string and the output will be in Capital Letters:

    DELIMITER //

    CREATE DEFINER=`root`@`%` FUNCTION `LCAPITAL`(eCADENA VARCHAR(150)) RETURNS varchar(150) CHARSET latin1
        DETERMINISTIC
    BEGIN

    DECLARE vPOSICION INT DEFAULT 0; 
    DECLARE vTMP VARCHAR(150) DEFAULT ''; 
    DECLARE vRESULTADO VARCHAR(150) DEFAULT '';
    DECLARE vCADENA VARCHAR(150) DEFAULT '';
    IF eCADENA IS NULL THEN
        SET vRESULTADO='';
        RETURN vRESULTADO;
    ELSE
        SET vCADENA=LCASE(eCADENA);
        REPEAT 
            SET vPOSICION=LOCATE(' ', vCADENA); 
            IF vPOSICION=0 THEN 
                SET vPOSICION=CHAR_LENGTH(vCADENA); 
            END IF; 
            SET vTMP=LEFT(vCADENA,vPOSICION); 
            IF CHAR_LENGTH(vTMP) < 4 THEN 
                SET vRESULTADO=CONCAT(vRESULTADO, vTMP); 
            ELSE 
                SET vRESULTADO=CONCAT(vRESULTADO, UPPER(LEFT(vTMP,1)),SUBSTRING(vTMP,2)); 
            END IF; 
            SET vCADENA=RIGHT(vCADENA,CHAR_LENGTH(vCADENA)-vPOSICION); 
        UNTIL CHAR_LENGTH(vCADENA) = 0 
        END REPEAT; 
        RETURN CONCAT(UPPER(LEFT(vRESULTADO,1)),MID(vRESULTADO,2,150)); 
    END IF;
    END

    //
    DELIMITER ;

Example:

    INSERT INTO pdone.reps 
        (veeva_rep_id,display_name,username,`first`,`last`,email) 
    SELECT 
        Id, 
        CONCAT(LCAPITAL(firstname),' ',LCAPITAL(lastname)) AS display_name,
        username, 
        firstname, 
        lastname, 
        email 
    FROM veeva.`user` 
    WHERE Id = "00580000003UB5VAAW";

Mysql – How to replace an empty date value

For starters, your GUI shouldn't matter, but it's normally good to use the CLI - i.e. the mysql client - or at least as a backup in case of results you are having problems interpreting.
Second, have you tried IS NULL instead of the = comparison?
Third, what's wrong with NULL? Normally, you should use the appropriate datatype - why is NULL a problem? If you don't know the date of birth, there's nothing wrong with it. For example, if you want to perform an AVG calculation, you'll have problems. Your population will appear much older than it really is. NULLs however will be ignored in such a calculation, so no anomalous results (the same goes for all such calculations).