This query (or something like it) should do the job for you: update temp11 set description = replace(description, 'EN', 'UP');
Naturally, you need to change 'temp11' to your table name and 'description' to the name of the column containing the data you want to change. The replace() function is documented in the manual on this page: http://dev.mysql.com/doc/mysql/en/string-functions.html I've tested this query and it works fine. Here's the script I used: use tmp; select 'Drop/Create table'; drop table if exists temp11; create table if not exists temp11 (idno smallint not null, description varchar(10) not null, primary key(idno)); select 'Populate temp11'; insert into temp11 (idno, description) values (1, 'EN05000'), (2, 'EN05001'), (3, 'EN05002'); select 'Display temp11'; select * from temp11; select 'Update temp11'; update temp11 set description = replace(description, 'EN', 'UP'); select 'Display modified temp11'; select * from temp11; The other approach that comes to mind would be to unload all your data to a flat file via mysqldump, edit the flat file with a text editor so that all 'EN' become 'UP', then reload the modified data back into the database. A lot more work but still do-able if there are some gotchas in the data that you haven't told us about. Rhino ----- Original Message ----- From: "Scott Purcell" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Tuesday, June 07, 2005 11:57 AM Subject: change data prefix query Hello, I have a table that has a varchar column in which I need to change a prefix for all records. Currently there are about 500 records and I did not want to do this by hand. It looks like this: [data here ] UP05000 UP05001 UP05002 UP05003 The identifier has now changed to EN so each records needs to be EN05000 EN05001 EN05002 etc. Can this be done with a query syntax? Or do I need to do this manually. Thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.6.5 - Release Date: 07/06/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.6.5 - Release Date: 07/06/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
