CODEWITHSUNDEEP
sqlsql-servert-sqlinsert-into
Slappywag
Slappywag

Reputation: 1223

Inserting a unique int into a non primary key field

I'm trying to make an SQL query using INSERT INTO to copy a number of rows from a table, but I want one of the fields (which isn't a primary key) to have a new, unique, int (I know this is terrible database design, but I can't change the structure of the DB).

Here is a simplified example of what I'm trying to do:

    INSERT INTO Mytable
        (field1
        ,field2
        ,field3
        ,nonKeyUniqueInt)

    SELECT
        (field1
        ,field2
        ,field3
        ,(SELECT MAX(nonKeyUniqueInt)+1 FROM mytable)
    FROM
        mytable
    WHERE
        (conditions)

However this doesn't work because the SELECT MAX query only runs once, giving all my new rows the same value for that field. Given the following rows to copy:

field1 field2 field3 nonKeyUniqueInt
x      y      z      1
a      b      c      2

I get output of:

field1 field2 field3 nonKeyUniqueInt
x      y      z      1
a      b      c      2
x      y      z      3
a      b      c      3

Is what I'm trying to do possible?

Upvotes: 0

Views: 116

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

The problem is that the subquery gets evaluated once for the insert, not once per row. The solution is to use row_number():

INSERT INTO Mytable(field1, field2, field3, nonKeyUniqueInt)
    SELECT field1, field2, field3,
           x.maxk + row_number() over (order by (select NULL))
    FROM mytable CROSS JOIN
         (SELECT MAX(nonKeyUniqueInt) as maxk FROM mytable) x
    WHERE (conditions);

I moved the max calculation to the FROM clause to make it clear that it is evaluated only once.

Upvotes: 2

Related Questions