I have to update all the rows of a Column in a table. Here is my query

Question

So My tables are

People:

People_name(VARCHAR 100), 
People_id (INT, PRIMARY KEY AUTO INCREMENT) 

Tracks:

Track_id(INT PRIMARY KEY), 
People_id_references (int ,Foreign key references People(people_id)), 
Track_title(VARCHAR 100)

top100:

artist varchar(100), 
id(int, primary key), 
track_title(varchar100)

All the columns in above tables are having values, except in people_id_references(this is in tracks table). top100 has many other columns, but for the sake of clarity I did not mention them, and it is no way connected to people or tracks tables. The data from top100 is present in track and people tables, this is done using some insert queries. I want to import values from people_id (it is in People table).

So my query is:

UPDATE tracks 
SET People_id_Reference = (SELECT People_id 
                           FROM People 
                           RIGHT JOIN top100 
                           ON 
                           People_name=top100.artist LIMIT 1) 
                           WHERE People_id_Reference IS NULL;

But that inserts same people_id into all rows of People_id_Reference. Is there any other way?

Answer 1

Try this:

UPDATE tracks t 
INNER JOIN top100 t1 ON t.track_title = t1.track_title 
LEFT JOIN People p ON t1.artist = p.People_name 
SET t.People_id_Reference = p.People_id 
WHERE t.People_id_Reference IS NULL;