Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Please add your table structure in your question with sample data. –  Saharsh Shah Nov 26 '12 at 6:20
    
Unless there is a trigger on tracks, your UPDATE will never insert new rows into the table. Could you show a few sample rows from tracks and people, showing what do you want to update, and what would be the desired result? –  dezso Nov 26 '12 at 6:55
add comment

1 Answer

up vote 0 down vote accepted

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;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.