UPDATE from SELECT complains about more that one value returned

Question

I have this data structure:

request
=======
building_id
lot_code

building
========
building_id
lot_id

lot
===
lot_id
lot_code

The request table is missing the value for the building_id column and I want to fill it in from the other tables. So I've tried this:

UPDATE request
SET building_id = (
    SELECT bu.building_id
    FROM building bu
    INNER JOIN lot lo ON bu.lot_id=lo.lot_id
    WHERE lo.lot_code = request.lot_code
);

But I'm getting this error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Is it due to wrong syntax? The data model allows more than one building per lot but actual data doesn't contain such cases so there should be at most one building_id per lot_code.

Answer 1

use top 1 in your query as in example below.

UPDATE request
SET building_id = (
    SELECT top 1 bu.building_id
    FROM building bu
    INNER JOIN lot lo ON bu.lot_id=lo.lot_id
    WHERE lo.lot_code = request.lot_code
);

Answer 2

probably you mean

UPDATE request
SET building_id = bu.building_id
FROM building bu
    INNER JOIN lot lo ON bu.lot_id=lo.lot_id
WHERE lo.lot_code = request.lot_code