Creating a new many-to-many table

Question

I have a database Class_Books which links ISBNs (from Books table) to Class_ID's (from Classes table). I'm changing my Books table so the primary key is a Book_ID (autoincrement INT) instead of the ISBN. Is there a way to update Class_Books so it uses Book_ID now?

Answer 1

MySQL supports a multi-table UPDATE syntax that makes this type of work a lot easier.

ALTER TABLE Class_Books ADD COLUMNS Book_Id INT; -- nullable

UPDATE Books b JOIN Class_Books cb ON b.ISBN = cb.ISBN
SET cb.Book_Id = b.Book_Id;

Then you can use ALTER TABLE to change the constraints, make cb.Book_Id NOT NULL, and drop cb.ISBN.

PS: I recommend calling Class_Books a many-to-many table or an intersection table. The word relation means something different in relational theory, and it has nothing to do with relationships between tables.