Good afternoon group my query is the following I have these tables
In the table books is the field lib_autor this field has a lot of records 3689 to be exact each one with its respective author (but these are repeated) and what I want is to replace those names with the id stored in the authors table aut_id since in this table each author has their id and they do not repeat themselves, they could tell me some query or something that does what I'm looking for I would appreciate it a lot.
The first thing you should do is add the column "lib_autor_id" to the table "books".
ALTER TABLE 'libros' ADD 'lib_autor_id' INT(11) NOT NULL AFTER 'lib_titulo';
Next, you must make the relationship between the tables "books" and "authors".
UPDATE 'libros' l LEFT JOIN 'autores' a ON l.lib_autor = a.aut_autor
SET l.lib_autor_id = a.aut_id;
And finally, after making sure that the "lib_autor" column of the "books" table is no longer necessary, you delete it.
ALTER TABLE 'libros' DROP 'lib_autor';
IMPORTANT: Do not forget to make a backup of the database before modifying it.