Page 1 of 1

Mass update SQL

Posted: Thu Jun 25, 2009 3:57 pm
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

Re: Mass update SQL

Posted: Thu Jun 25, 2009 6:16 pm
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.

Re: Mass update SQL

Posted: Fri Jun 26, 2009 3:31 pm
by vtvstv
Thankyou for your help. I really appreciate it!!!

Best Regards

Kai