How can I copy, modify, records and then insert them back into the same table with a unique ID?

Question

The "fruit" table has a column for unique IDs for each row, along with other columns. I need to copy some of the rows from "fruit", and insert it back into "fruit" with some changes to the columns, generating the next unique ID for each record with an increment of 1 from the tables max ID.

INSERT INTO fruit (id,size,color,type,classification) SELECT MAX(id) OVER() + ROW_NUMBER () OVER(ORDER BY id),size,color,type,'melon') FROM fruit WHERE classification=citrus

There are other classification that have unique IDs, so when I created the query and tested it, it created new melon records, but assigned them IDs higher than citrus, but not higher than the other classifications, resulting in duplicate IDs.

Answer 1

The above answers are correct, but don't really show how to do it.

INSERT INTO fruit (id, size, color, type, classification) 
 (SELECT (select MAX(id) from fruit) + row_number() over(order by id),
         size,
         color,
         type,
         'melon' 
  FROM fruit 
  WHERE classification='citrus')

This replaces MAX(id) with select MAX(id) from fruit. You still need the row_number () over() in case you have multiple rows, because select MAX(id) from fruit is only evaluated once, and each row inserted will need a new id. I also corrected some syntax errors in your above query. The sub-selects (there are two of them) need to be enclosed by parentheses (), and the literal citrus needs to be enclosed by single quotes '.

Answer 2

The behaviour you see is exactly what you told Db2 to do. If you want the highest id regardless of what fruit it is assigned to, leave out the OVER and WHERE clauses, and simply select max(id) from fruit.