Deleting HTML from WordPress posts using SQL failing without error

Question

I am trying to delete a string of HTML from all wordpress posts and pages (but posts first)

I have a query where I find all instances where the string exists, it looks like this and runs fine:

SELECT * FROM dmwbg_posts WHERE post_content
                                 LIKE
                             '%<p>Stöd oss</p><p>Vårt arbete</p><p>Om MyRight</p><p>Jobb och praktik</p><p>Nyheter</p><p>Kontakta oss</p><p>Besöks- och postadress: MyRight Liljeholmen 7A 117 63<br /> Telefon: 08-505 776 00   |   Epost: <a href="mailto:[email protected]">[email protected]</a>   <span>|   Org. nr 802402-9376</span></p><p><strong>Stöd MyRight med en gåva</strong><br /> Swish: 123 900 11 08<br /> <span>Plusgiro: 90 01 10-8</span></p><p>Besöks- och postadress: <br />MyRight Liljeholmen 7A, 117 63 Stockholm<br /> Telefon: 08-505 776 00<br /> Epost: <a href="mailto:[email protected]">[email protected]<br /> </a><span>Org. nr 802402-9376</span></p><p><strong>Stöd MyRight med en gåva</strong><br /> Swish: 123 900 11 08<br /> <span>Plusgiro: 90 01 10-8</span></p>%';

This query returns 126 rows, I only want to delete the specific string. Not all the data from the rows.

To do this I have written the following query:

UPDATE dmwbg_posts
SET
    post_content = ''
WHERE
    post_content = '%<p>Stöd oss</p><p>Vårt arbete</p><p>Om MyRight</p><p>Jobb och praktik</p><p>Nyheter</p><p>Kontakta oss</p><p>Besöks- och postadress: MyRight Liljeholmen 7A 117 63<br /> Telefon: 08-505 776 00   |   Epost: <a href="mailto:[email protected]">[email protected]</a>   <span>|   Org. nr 802402-9376</span></p><p><strong>Stöd MyRight med en gåva</strong><br /> Swish: 123 900 11 08<br /> <span>Plusgiro: 90 01 10-8</span></p><p>Besöks- och postadress: <br />MyRight Liljeholmen 7A, 117 63 Stockholm<br /> Telefon: 08-505 776 00<br /> Epost: <a href="mailto:[email protected]">[email protected]<br /> </a><span>Org. nr 802402-9376</span></p><p><strong>Stöd MyRight med en gåva</strong><br /> Swish: 123 900 11 08<br /> <span>Plusgiro: 90 01 10-8</span></p>%';

This query runs without any errors, how ever it does not not replace any of the data. the query result shows:

Query OK, 0 rows affected (0.02 sec)
Rows matched: 0  Changed: 0  Warnings: 0

Running my SELECT query after running my UPDATE query shows the same number of results, and the data is still showing up on the WordPress site. I'm not sure what I am doing wrong.

I am running MariaDB version 10.1.44-MariaDB-0ubuntu0.18.04.1

Akhilesh Mishra Akhilesh Mishra · Accepted Answer · 2020-05-21T17:10:24

In your update query you are using % wildcard with = which works with LIKE. Thats why it is updating 0 records. in your case you should use replace() function

update table_name set col_name=replace(col_name,'From_text', 'to_text') where condition

with your values it should be like

update dmwbg_posts set post_content = replace(post_content, '<p>Stöd oss</p><p>Vårt arbete</p><p>Om MyRight</p><p>Jobb och praktik</p><p>Nyheter</p><p>Kontakta oss</p><p>Besöks- och postadress: MyRight Liljeholmen 7A 117 63<br /> Telefon: 08-505 776 00   |   Epost: <a href="mailto:[email protected]">[email protected]</a>   <span>|   Org. nr 802402-9376</span></p><p><strong>Stöd MyRight med en gåva</strong><br /> Swish: 123 900 11 08<br /> <span>Plusgiro: 90 01 10-8</span></p><p>Besöks- och postadress: <br />MyRight Liljeholmen 7A, 117 63 Stockholm<br /> Telefon: 08-505 776 00<br /> Epost: <a href="mailto:[email protected]">[email protected]<br /> </a><span>Org. nr 802402-9376</span></p><p><strong>Stöd MyRight med en gåva</strong><br /> Swish: 123 900 11 08<br /> <span>Plusgiro: 90 01 10-8</span></p>', '')
WHERE post_content  LIKE '%<p>Stöd oss</p><p>Vårt arbete</p><p>Om MyRight</p><p>Jobb och praktik</p><p>Nyheter</p><p>Kontakta oss</p><p>Besöks- och postadress: MyRight Liljeholmen 7A 117 63<br /> Telefon: 08-505 776 00   |   Epost: <a href="mailto:[email protected]">[email protected]</a>   <span>|   Org. nr 802402-9376</span></p><p><strong>Stöd MyRight med en gåva</strong><br /> Swish: 123 900 11 08<br /> <span>Plusgiro: 90 01 10-8</span></p><p>Besöks- och postadress: <br />MyRight Liljeholmen 7A, 117 63 Stockholm<br /> Telefon: 08-505 776 00<br /> Epost: <a href="mailto:[email protected]">[email protected]<br /> </a><span>Org. nr 802402-9376</span></p><p><strong>Stöd MyRight med en gåva</strong><br /> Swish: 123 900 11 08<br /> <span>Plusgiro: 90 01 10-8</span></p>%';

Deleting HTML from WordPress posts using SQL failing without error

1 Answers