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?

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