Tomanow Tomanow - 1 year ago 62
SQL Question

Subquery returns more than 1 row with update

I have been wrestling this issue for hours to no avail. I've been looking at many of the 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 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
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 Source

Use the LIMIT clause. This will limit the amount of results to one row.

UPDATE positions 
SET client_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.