Mass update SQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
vtvstv
Forum Newbie
Posts: 10
Joined: Sun May 24, 2009 3:19 am

Mass update SQL

Post by vtvstv »

OK, I have a MySQL db and a table with around 4000-5000 records and in amongst those records around 300 that need to be updated, basically the data held at present is for example "The Kinks", but I need it to become "Kinks, The" so I need some sort of script that will track down all records that begin with "The " and replace the string at the end. Any ideas???

As always any help on this will be greatly appreciated!!!

Best Regards

Kai
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Mass update SQL

Post by McInfo »

An example table

Code: Select all

SELECT * FROM `the`;
# +----+------------+
# | id | title      |
# +----+------------+
# |  1 | The Kinks  |
# |  2 | Knickers   |
# |  3 | The Browns |
# |  4 | Theodore   |
# +----+------------+
A preview of the changes

Code: Select all

SELECT CONCAT(SUBSTR(`title`, 5), ', The') AS `preview` FROM `the` WHERE SUBSTR(`title`, 1, 4) = 'The ';
# +-------------+
# | preview     |
# +-------------+
# | Kinks, The  |
# | Browns, The |
# +-------------+
The UPDATE query

Code: Select all

UPDATE `the` SET `title` = CONCAT(SUBSTR(`title`, 5), ', The') WHERE SUBSTR(`title`, 1, 4) = 'The ';
The table after the changes

Code: Select all

SELECT * FROM `the`;
# +----+-------------+
# | id | title       |
# +----+-------------+
# |  1 | Kinks, The  |
# |  2 | Knickers    |
# |  3 | Browns, The |
# |  4 | Theodore    |
# +----+-------------+
Edit: This post was recovered from search engine cache.
Last edited by McInfo on Wed Jun 16, 2010 12:25 pm, edited 1 time in total.
vtvstv
Forum Newbie
Posts: 10
Joined: Sun May 24, 2009 3:19 am

Re: Mass update SQL

Post by vtvstv »

Thankyou for your help. I really appreciate it!!!

Best Regards

Kai
Post Reply