Manuel Hoffmann Manuel Hoffmann - 5 months ago 11
SQL Question

Recalculate subquery on INSERT for each row

When I have the following table:

CREATE TABLE Acts ( ParentID int, ActID int, Text nvarchar(255) )
---------------------
1 | 1 | "Hello"
1 | 2 | "Hello again"
2 | 1 | "lol"
2 | 2 | "rofl"


where ActID is a consecutive number based on the ParentID.
and I need to insert new row for each of a subset of rows like:

INSERT INTO Acts
SELECT ParentID, (SELECT MAX(ActID) + 1 FROM Acts), Text FROM Acts WHERE Text LIKE 'Hello%'


I got

1 | 1 | "Hello"
1 | 2 | "Hello again"
1 | 3 | "Hello"
1 | 3 | "Hello again"
...


I want:

1 | 1 | "Hello"
1 | 2 | "Hello again"
1 | 3 | "Hello"
1 | 4 | "Hello again"


I quickly realised that the Subquery is only evaluated before the INSERTS take place, that's why it always returns the same Max number. Is there a way to insert the Results one by one so that the subquery may update OR to specify that the subquery needs to update after each row?

Answer

Try this, it will just get the max ActID and adds 1,2,3 in seq to it.

INSERT INTO Acts
SELECT ParentID, (SELECT MAX(ActID) FROM Acts) + ROW_NUMBER() OVER(order by ParentID), Text 
FROM Acts
WHERE Text LIKE 'Hello%'
Comments