I have a situation with two tables where one has a foreign key pointing to the other table (simplified) schema:

CREATE TABLE table1 (
   name VARCHAR(64) NOT NULL,
   PRIMARY KEY(name)
); 

CREATE TABLE table2 (
   id SERIAL PRIMARY KEY,
   table1_name VARCHAR(64) NOT NULL REFERENCES table1(name)
);

Now I regret using the name column as primary key in table1 - and would like to add integer serial key instead. Since I already have data in the database I guess I need to do this carefully. My current plan is as follows:

1. Drop the foreign key constraint: table2(name) with ALTER TABLE table2 DROP CONSTRAINT table2_table1_name_fkey;
2. Drop the primary key constraint on table1(name) with ALTER TABLE table1 DROP CONSTRAINT name_pkey;.
3. Add a unique constraint on table1(name) with ALTER TABLE table1 ADD UNIQUE(name);
4. Add a automatic primary key to table1 with ALTER TABLE table1 ADD COLUMN ID SERIAL PRIMARY KEY;.
5. Add a new column table1_id to table2 with ALTER TABLE table2 ADD COLUMN table1_id INT;
6. Update all rows in table2 - so that the new column (which will be promoted to a foreign key) gets the correct value - as inferred by the previous (still present) foreign key table1_name.

I have completed steps up to an including step 5, but the UPDATE (with JOIN?) required to complete 6 is beyond my SQL paygrade. My current (google based ...) attempt looks like:

UPDATE
  table2
SET
  table2.table1_id = t1.id
FROM
  table1 t1
  LEFT JOIN table2 t2
     ON t2.table1_name = t1.name;

CodePudding user response：

You do not need JOIN in UPDATE.

UPDATE 
  table2 t2
SET
  table1_id = t1.id
FROM
  table1 t1
WHERE 
  t2.table1_name = t1.name;