UPDATE from SELECT complains about more that one value returned
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 b开发者_开发问答e at most one building_id
per lot_code
.
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
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 );
精彩评论