MySQL UPDATE to replace text in LONGTEXT field

mysql-5query

I have a WordPress table wp_posts and I'd like to run an UPDATE that will replace a url from my old domain to my new domain.

For example, let's say that one record in the field post_content has the following content:

This is my <a href="http://www.my-old-site.com/link/to/some/page">old web</a> site.
<img src="http://www.my-old-site.com/wp-content/upload/2012/02/my-image-file.jps />

I'd like that to become:

This is my <a href="http://www.my-new-site.com/link/to/some/page">old web</a> site.
<img src="http://www.my-new-site.com/wp-content/upload/2012/02/my-image-file.jps />

I've tried the following query

UPDATE wp_posts AS w` 
SET w.post_content = REPLACE(w.post_content, 'my-old-site.com', 'my-new-site.com');

but I don't get any result.

Any idea how to fix it?

Best Answer

There is an error in your query near

AS w`

Alias name "w" should instead be

`w`

Try this:

UPDATE wp_posts AS `w` 
SET w.post_content = REPLACE(w.post_content, 'my-old-site.com', 'my-new-site.com');

Related Solutions

Sql-server – Query to normalize table/combine row text

This should work, I will clean it up later so its more efficient.

DECLARE @Old TABLE ( 
  id         INT, 
  rank       INT, 
  linenumber INT, 
  sometext   VARCHAR(1000)) 
DECLARE @New TABLE ( 
  id           INT, 
  rank         INT, 
  combinedtext VARCHAR(1000)) 


;WITH combinedresults(ctid, id, rank, linenumber, combinedtext) 
     AS (SELECT 0, 
                id, 
                rank, 
                linenumber, 
                CAST (sometext AS VARCHAR(8000)) 
         FROM   @Old o 
         WHERE  NOT EXISTS (SELECT TOP 1 1 
                            FROM   @Old 
                            WHERE  id = o.id 
                                   AND rank = o.rank 
                                   AND linenumber < o.linenumber) 
         UNION ALL 
         SELECT ctid + 1, 
                o.id, 
                o.rank, 
                o.linenumber, 
                ct.combinedtext + o.sometext 
         FROM   @Old o 
                INNER JOIN combinedresults ct 
                  ON ct.id = o.id 
                     AND ct.rank = o.rank 
         WHERE  o.linenumber > ct.linenumber) 

UPDATE n 
SET    combinedtext = ct.combinedtext 
FROM   @New n 
       INNER JOIN (SELECT n.id, 
                          n.rank, 
                          MAX(o.rank) orank 
                   FROM   @new n 
                          INNER JOIN @Old o 
                            ON n.id = o.id 
                               AND o.rank <= n.rank 
                   GROUP  BY n.id, 
                             n.rank) r 
         ON n.id = r.id 
            AND n.rank = r.rank 
       INNER JOIN (SELECT id, 
                          ct.rank, 
                          MAX(ctid) ctid 
                   FROM   combinedresults ct 
                   GROUP  BY ct.id, 
                             ct.rank) r2 
         ON r2.id = r.id 
            AND r2.rank = r.orank 
       INNER JOIN combinedresults ct 
         ON r.id = ct.id 
            AND ct.rank = r.orank 
            AND ct.ctid = r2.ctid 

SELECT * 
FROM   @New

MySQL: Trying to update to 5.5

The error means mysqld does not have the access rights to the directory.

When you recreated /var/lib/mysql, you need to make sure the /var/lib/mysql directory is owned and writeable by the mysql user.

Best Answer

Related Solutions

Sql-server – Query to normalize table/combine row text

MySQL: Trying to update to 5.5

Related Question