Subquery returns more than 1 row with update

Question

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:

UPDATE positions 
SET client_id = (SELECT clients.id 
FROM clients 
WHERE clients.file_name = positions.file_name)

It returns.

1242 - Subquery returns more than 1 row

Thanks in advance for any help!

Answer 1

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.