I have a 1:1 relation between data that was not an explicit foreign key in the database. E.g.
Table 1 has a once, b twice and c once
id value 1 a 2 b 3 b 4 c
Table 2 also has a once, b twice and c once
id value 5 a 6 b 7 c 8 b
I want to create a foreign key from Table 1 to Table 2 when the value is the same:
Expected Table 1 after query
id value fk 1 a 5 2 b 6 3 b 8 4 c 7
I thought about using JOINs but on t1.value = t2.value but those create the cartesian product, and I end up with
id value fk 1 a 5 2 b 6 2 b 8 3 b 6 3 b 8 4 c 7
How can I populate the fk field in such a way that different rows with the same value receive have different fk?
Advertisement
Answer
You need to JOIN your tables based on the row number of each value in each table; then you can select the id value from Table2 to set the fk column in Table1. First, add the new column:
ALTER TABLE Table1 ADD COLUMN fk INT REFERENCES Table2(id)
Then you can UPDATE Table1 with the appropriate id from Table2:
WITH CTE1 AS (
SELECT id, value,
ROW_NUMBER() OVER (PARTITION BY value) AS rn
FROM Table1
),
CTE2 AS (
SELECT id, value,
ROW_NUMBER() OVER (PARTITION BY value) AS rn
FROM Table2
)
UPDATE Table1
SET fk = CTE2.id
FROM CTE1
JOIN CTE2 ON CTE2.value = CTE1.value AND CTE2.rn = CTE1.rn
WHERE CTE1.id = Table1.id
Resultant Table1:
id value fk 1 a 5 2 b 6 3 b 8 4 c 7