Using SQL to do a Mass Update of a Website

Suppose you wanted to replace a whole bunch of content on your database-driven website. Maybe you want the search engines to re-index and update their caches so that the old content effectively disappears. In that case, you need to replace the content for a while before removing it.

Before you do something inefficient, like using your GUI CMS to painstakingly edit potentially thousands of posts or exporting a flat file and doing a search and replace with a text editor, remember that you have direct access via your database to all the data that is ultimately displayed in web browsers. Use SQL to manipulate it.

To totally obliterate the content and put something else in its place, just do an INSERT query on the relevant tables and fields, like this:

INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...)

Voila! Out with the old and in with the new!

Watch your logs, and after the spiders have visited the pages again, delete them entirely with a DELETE query. Let's say you wanted to delete every post whose post ID (pid) was less than 234 and that the content is in a table called "content_tbl."

DELETE from "content_tbl" WHERE pid < 234

Gone, all of it, probably in less than one second. No exporting and re-importing, no fuss, nice and clean. Another way to do it would be to use a timestamp to delete all content posted before a certain date. Anyway, it's a heck of a lot better than doing things the way they were done in a static html world.

But what if you want to retain the content while replacing only certain strings? Maybe you misspelled something throughout the site and you're terribly embarrassed about it. Instead of exporting flat files and doing search and replace or -- worse -- editing hundreds of entries by hand, you can simply dig into the back end of your site, the database, and do the whole thing quickly and cleanly with an SQL statement.

Go to your database and find the relevant tables and fields, the ones you want to replace. Run an UPDATE...= REPLACE query on the tables and columns that contain the data. Here's the syntax:

UPDATE table_name SET column_name = REPLACE (column_name, "misspelled string", "correctly spelled string")

If you run this query on every field that contains your misspelled string, it will replace every instance of "misspelled string" in your site with "correctly spelled string." Know thy database so that you run the query on the right columns, though!

One could get exceedingly fancy with these types of queries by adding WHERE statements and the like. The possibilities there are far beyond the scope of this post, endless really.

One caveat: Be meticulous and thorough. Know which tables require action and act upon them. The site you're looking at now has a database with dozens of tables behind it. Yours might have even more. Also remember, of course, to make a backup of your database before doing anything.

Much easier than the old way -- doing a search and replace on dozens of html files.

Hope that helped.

Find this article helpful? Stumble it!