Skip to content
Advertisement

Recreating relation between two tables

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

Demo on SQLFiddle

2 People found this is helpful
Advertisement