Member Avatar for andarivaadu1

Here is my query

 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 query inserts same people_id value into all rows of People_id_reference Can some on help me with this. Thanks in advance

  • 2 Contributors
  • 2 Replies
  • 206 Views
  • 2 Hours Discussion Span
  • Latest Post Latest Post by andarivaadu1

Dani AI

Generated

The behavior you saw is exactly the classic cause: the subquery you used returns one constant People_id for every updated row (LIMIT 1 plus no reference to the current Tracks row), so that single value is applied to all rows. was right to ask for the schema — with the structure you posted you should match Tracks -> top100 -> People so each track gets the correct People_id.

Preview the row-level matches first (do not run the UPDATE until you verify the results):

SELECT t.Track_id, t.Track_title, p.People_id, p.People_name
FROM tracks t
JOIN top100 tt ON tt.track_title = t.Track_title
JOIN People p   ON p.People_name = tt.artist
WHERE t.People_id_references IS NULL
LIMIT 100;

Preferred fix (MySQL UPDATE ... JOIN): this updates only rows that actually match and uses the correct People_id per track.

UPDATE tracks t
JOIN top100 tt ON tt.track_title = t.Track_title
JOIN People p   ON p.People_name = tt.artist
SET t.People_id_references = p.People_id
WHERE t.People_id_references IS NULL;

If you cannot do a join for some reason, use a correlated subquery that references the outer tracks row so the selected People_id varies per row:

UPDATE tracks t
SET t.People_id_references = (
  SELECT p.People_id
  FROM People p
  JOIN top100 tt ON tt.artist = p.People_name
  WHERE tt.track_title = t.Track_title
  LIMIT 1
)
WHERE t.People_id_references IS NULL;

Cautions and troubleshooting: back up the table or run inside a transaction so you can ROLLBACK if results are wrong. Verify string matching (trim/case/collation) — mismatched names will produce no joins or wrong joins. If People_name is not unique, decide which row is authoritative before using LIMIT 1. Run the preview SELECT above to confirm the mapping before executing the UPDATE.

Recommended Answers

All 2 Replies

Member Avatar for urtrivedi

what is ur table structure and what is ur purpose(what u want to update with what value)?

Only query is not enough to understand your problem.

Member Avatar for andarivaadu1

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 clarity sake I did not mention them, and it is now way connected to people or tracks table. The data from top100 is present in track and people table, this is done using some insert queries.I want to import values from people_id (it is in People table).

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.