How to change column type and existing rows using data from another table?

Question

PostgreSQL

I have two tables: progress and subjects. progress has a column subject of type text, and the table subjects has 2 columns: subject_id of type integer and subject of type text.
How can I change the type of the subject column in the progress table to integer, while also changing the existing records in this table?
I tried this option (renamed subject to subject_id for further assignment of a foreign key to it):

ALTER TABLE progress RENAME COLUMN subject TO subject_id;

ALTER TABLE progress ALTER COLUMN subject_id TYPE INTEGER USING (
SELECT subjects.subject_id 
FROM subjects, progress
WHERE progress.subject_id = subjects.subject
);

but this is not a valid query:
ERROR: Cannot use subquery in transformation expression
LINE 1: ...ogress ALTER COLUMN subject_id TYPE INTEGER USING (SELECT su...

Answer 1

ALTER TABLE progress ADD COLUMN subject_id INT;

UPDATE progress p SET p.subject_id = s.subject_id 
  FROM subjects s
 WHERE p.subject = s.subject;

-- ALTER TABLE progress DROP COLUMN subject;