Monday, 2 February 2009

Set foreign key data based on data in another table

Update Table3
Set ref = (Select a.id from Table1 a, Table2 b
Where a.name = b.name and b.id = Table3.ref)
DECLARE @T1 TABLE(
id INT,
v VARCHAR(50)
)

DECLARE @Load_T1 TABLE(
id INT,
v VARCHAR(50)
)

INSERT INTO @T1
SELECT 1,NULL
UNION SELECT 2, NULL
UNION SELECT 3, NULL

INSERT INTO @Load_T1
SELECT 1,'Test1'
UNION SELECT 3, 'Test3'

SELECT * FROM @T1
SELECT * FROM @Load_T1

UPDATE @T1
SET t.v = l.v
FROM @T1 t JOIN @Load_T1 l ON t.id = l.id

SELECT * FROM @T1


UPDATE @T1 t
SET t.v = (SELECT l.v
FROM @Load_T1 l JOIN @T1 t ON l.id = t.id
AND t.id = @T1.id)

SELECT * FROM @T1


Update Table3
Set ref = (Select a.id from Table1 a, Table2 b
Where a.name = b.name and b.id = Table3.ref)

No comments:

Post a Comment