2

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?

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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