UPDATE from SELECT complains about more that one value returned
- by Álvaro G. Vicario
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.
