I have been wrestling this issue for hours to no avail. I've been looking at many of the stackoverflow.com questions related to subqueries returning more than one row, but have not been able to find one that helps in my case.
I have two tables: clients and positions. I am trying to UPDATE positions.client_id with clients.id WHERE clients.file_name = positions.file_name.
Basically I have a list of positions that need to be assigned to clients. Every client has multiple positions, but every position is assigned to exactly one client. In other words, client_id is not unique in positions, but id is unique (primary key) in clients.
Here's what I have been trying different variations with:
SET client_id = (SELECT clients.id
WHERE clients.file_name = positions.file_name)
1242 - Subquery returns more than 1 row
Use the LIMIT clause. This will limit the amount of results to one row.
UPDATE positions SET client_id = ( SELECT clients.id FROM clients WHERE clients.file_name = positions.file_name LIMIT 1 )
Keep in mind that the best practice is typically NOT to use subqueries to achieve what you want in SQL.