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