Update record's foreign key field based on a newly inserted record's primary key

user_952310 0
Viewed 1985

For each record in table A I want to update the foreign key value of one of the fields based on new inserted record's scope_identity in table B.

I need to create a new record in table B for each record in table A in order to receive a foreign key(scope_identity) value.

For example, for each row in the following table I want to update the null Foreign Key field based on creating a new row/foreign key in Table B.

Table A:

|Id|ForeignKey|
|1 |NULL      |
|2 |NULL      |
|3 |NULL      |
|4 |NULL      |
|5 |NULL      |

As a pseudo code I thought of something like this sql:

update TableA 
set ForeignKey = (INSERT INTO TableB VALUES (value1) select SCOPE_IDENTITY())

Any idea?

sql-server sql
3 Answers

Another method I found is using ROW_NUMBER function to arbitrarily join the tables, then use that derived query to update the FK. The idea is there isnt a relationship yet between tableA and tableB we just need link them to grab the new foreign keys. Joining them on something arbitrary like ROW_NUMBER gives us a method to pick out a single row to grab the Id from.

Create the new records first

INSERT INTO TableB VALUES (value1)

Write the query that arbitrarily joins them on ROW_NUMBER (we use this in the next step)

SELECT
    tableARows.Id, 
    tableBRows.TheNewId
FROM
(
    SELECT Id, Row =  ROW_NUMBER() OVER (ORDER BY Id) FROM tableA
)tableARows INNER JOIN
(
    SELECT TheNewId, Row = ROW_NUMBER() OVER (ORDER BY TheNewId) FROM tableB
)tableBRows ON tableARows.Row = tableBRows.Row

And then a full UPDATE statement to populate tableA with new FK's from tableB

UPDATE tableA 
SET tableA.ForeignKey = LinkedKeys.TheNewId
FROM
(
    SELECT
        tableARows.Id, 
        tableBRows.TheNewId
    FROM
    (
        SELECT Id, Row =  ROW_NUMBER() OVER (ORDER BY Id) FROM tableA
    )tableARows INNER JOIN
    (
        SELECT TheNewId, Row = ROW_NUMBER() OVER (ORDER BY TheNewId) FROM tableB
    )tableBRows ON tableARows.Row = tableBRows.Row
)LinkedKeys INNER JOIN
tableA  ON tableA.Id = LinkedKeys.Id

Here are example table definitions I used.

CREATE TABLE tableA (Id INT, ForeignKey INT)
INSERT INTO tableA VALUES (100,NULL),(200,NULL),(300,NULL)


CREATE TABLE tableB (TheNewId INT)
INSERT INTO tableB VALUES(5000),(6000),(7000)
user_13637800
Related