Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need a SQL query that can update ID fields for all records in a table from their previous values to a new value based on that previous value.

For example, if the ID is 1, I want to change it to 9991 - prepending '999' to every ID in the table.

What is the most efficient means of doing this?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

A couple options come to mind. If you have a charish data type:

update table set column=concat('999',column);

If you have an integer type find the max value, let's say it's under 10,000 then

update table set column=9990000+column
share|improve this answer
    
Thanks, the second suggestion worked well for the id field (which is a bigint(20) in my case). I'm glad you pointed out to find the max value first. I needed to update these columns in this way because I will be importing them into another duplicate table (duplicate structure; not duplicate records) and did not want the import to fail because of duplicate records. –  Micah - ps I love you Mar 28 '12 at 1:39
    
If the tables in on the same server you could just run insert into newtable (col2, col3, .. colN) select col2, col3, colN from oldtable; Where you leave out your id field in bot the values list and the select list. This assumes your id column is an auto_increment field. Then you just let mysql assign new ids to the rows in the new table –  atxdba Mar 28 '12 at 2:05
    
@atxdba: But ths (your comment) way will not take care of any referencing columns in other tables. While your Update will (as long as there is an ON UPDATE CASCADE). –  ypercube Mar 28 '12 at 16:13
    
I agree with ypercube's comment. –  atxdba Mar 28 '12 at 17:53
    
Yes, in my case, another table was referencing these values. I had to perform the same update procedure on that table as well. –  Micah - ps I love you Mar 29 '12 at 0:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.