SQL: Search and Replace Text

The way to do a "search and replace" function in SQL is to use the UPDATE command along with the REPLACE command.

The syntax is:
UPDATE table_name SET column_name = REPLACE ('text sought','new text');

For example, I have some old CMS posts that have missing triangle gifs, and I want to replace them with star gifs in WordPress:

UPDATE wp_posts SET post_content = REPLACE (post_content, '/feature/film_reviews/triangle.gif'', '/wp-content/uploads/2015/01/star.gif'');

You should probably do a backup of your database first, and will probably want to test out your code on a single record first, by adding a WHERE clause like: WHERE ID= 99999, or WHERE ID BETWEEN 1000 AND 1005.

How to Change / Reset MySQL Auto Incement

I goofed up my SQL database one day, long, long ago, and it's been bugging me ever since.

I have a database that has different sections of a website.

Ex.

Key   Name
1     Cars
2     Houses
3     Pets
666   Junk

Well, setting that 666 for the (Auto_Incement) key for the Junk column was a nice idea at the time, but when I added new sections, they'd jump forward to 667, 668, 669, etc. Even after I deleted the whole record for 666 Junk. I would have to go in and manually set the new records to a lower number.

So, here's how to fix such a thing:

First, delete the erroneous entry from your table.

DELETE FROM exampleTableName WHERE Key = 666 LIMIT 1

Then enter this ...

ALTER TABLE exampleTableName AUTO_INCREMENT = 1

This does not reset the Auto_Increment to 1. Instead (if I had deleted Key 666), the next auto increment would be set to 4.

Exactly what I needed. Hope it helps you.