How should I update ID field values for all records based on previous values?

Question

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?

atxdba · Accepted Answer · 2012-03-28 00:59:32Z

3

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

answered Mar 28, 2012 at 0:59

atxdba

5,2635 gold badges41 silver badges62 bronze badges

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 Bolen
Commented Mar 28, 2012 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
Commented Mar 28, 2012 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ᵀᴹ
Commented Mar 28, 2012 at 16:13
I agree with ypercube's comment.
– atxdba
Commented Mar 28, 2012 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 Bolen
Commented Mar 29, 2012 at 0:33

Add a comment |

1 Answer 1