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
Mass update SQL
Moderator: General Moderators
Re: Mass update SQL
An example table
A preview of the changes
The UPDATE query
The table after the changes
Edit: This post was recovered from search engine cache.
Code: Select all
SELECT * FROM `the`;
# +----+------------+
# | id | title |
# +----+------------+
# | 1 | The Kinks |
# | 2 | Knickers |
# | 3 | The Browns |
# | 4 | Theodore |
# +----+------------+Code: Select all
SELECT CONCAT(SUBSTR(`title`, 5), ', The') AS `preview` FROM `the` WHERE SUBSTR(`title`, 1, 4) = 'The ';
# +-------------+
# | preview |
# +-------------+
# | Kinks, The |
# | Browns, The |
# +-------------+Code: Select all
UPDATE `the` SET `title` = CONCAT(SUBSTR(`title`, 5), ', The') WHERE SUBSTR(`title`, 1, 4) = 'The ';Code: Select all
SELECT * FROM `the`;
# +----+-------------+
# | id | title |
# +----+-------------+
# | 1 | Kinks, The |
# | 2 | Knickers |
# | 3 | Browns, The |
# | 4 | Theodore |
# +----+-------------+
Last edited by McInfo on Wed Jun 16, 2010 12:25 pm, edited 1 time in total.
Re: Mass update SQL
Thankyou for your help. I really appreciate it!!!
Best Regards
Kai
Best Regards
Kai