"A subquery has returned not exactly one row" error

Question

I've been searching the web all night trying to find an answer to this... No luck so far. All other solutions I have tried have resulted in syntax errors.

I'm trying to run an update statement to update location_id values for a selection items. I have two tables: an existing INVENTORY table and a TEMP_INV table containing a subset of item_id values and new location_id values. I want to update the current location_id values in the INVENTORY table with the new location_id values in the TEMP_INV table, but only for the items in the TEMP_INV table.

INVENTORY table

item_id    location_id

123453     12-099
123454     12-100
123456     12-101
123457     12-102
123458     12-103

TEMP_INV table

item_id    location_id

123456     13-101
123457     13-102
123458     13-103

Desired results:

INVENTORY table

item_id    location_id

123453     12-099
123454     12-100
123456     13-101
123457     13-102
123458     13-103

I'm running the below update statement and receiving the error "284: A subquery has returned not exactly one row."

UPDATE inventory
SET location_id =
(SELECT location_id
FROM temp_inv
WHERE item_id=item_id)

That usually means that the subquery returned more than one row. :-) To find out which `location_id` is returning more than one row for your query, you could just do a count and group on the `location_id` and `item_id` of your `temp_inv` table. Any one with more than one will be your culprit. — Shawn, Nov 02 '19 at 04:29

score 1 · Answer 1 · answered Nov 02 '19 at 14:59

The problem is probably your correlation condition. Qualify column names!

UPDATE inventory
    SET location_id = (SELECT temp_inv.location_id
                       FROM temp_inv
                       WHERE inventory.item_id = temp_inv.item_id
                      );

If you still have a problem, you need to select one matching row -- or investigate why you have duplicates! One solution is limit, which I think Informix supports in subqueries:

UPDATE inventory
    SET location_id = (SELECT temp_inv.location_id
                       FROM temp_inv
                       WHERE inventory.item_id = temp_inv.item_id
                       LIMIT 1
                      );

Otherwise, aggregation works:

UPDATE inventory
    SET location_id = (SELECT MAX(temp_inv.location_id)
                       FROM temp_inv
                       WHERE inventory.item_id = temp_inv.item_id
                      );

Note: Your attempted query -- and all these -- will set the value to NULL for non-matching rows.

score 0 · Answer 2 · answered Nov 02 '19 at 04:45

0

Use join query:

UPDATE inventory i
INNER JOIN  temp_inv it
ON i.item_id    = it.item_id    
SET i.location_id= it.location_id;

answered Nov 02 '19 at 04:45

Divyesh patel

967
1
6
21

Vérace · Answer 3 · 2019-11-02T05:05:36.280

You could try something like this (see fiddle). I chose PostgreSQL as the exemplar because Informix is a product which descends from the Postgres stable (Michael Stonebraker was involved in both) and there are no Informix fiddles that I know about. These updates can be tricky because of the varying syntax amongst vendors.

Create and populate inventory table:

CREATE TABLE inventory
(
  item_id INTEGER NOT NULL,
  location_id VARCHAR (25) NOT NULL
);


INSERT INTO inventory VALUES (123453, '12-099'), (123454, '12-100'), (123456, '12-101'),
(123457, '12-102'), (123458, '12-103');

Same for temp_inv:

CREATE TABLE temp_inv
(
  item_id INTEGER NOT NULL,
  location_id VARCHAR (25) NOT NULL
);

INSERT INTO temp_inv VALUES (123456, '13-101'), (123457, '13-102'), (123458, '13-103');

Then run the following SQL:

UPDATE inventory
SET location_id = temp_inv.location_id
FROM temp_inv
WHERE inventory.item_id = temp_inv.item_id;

Result (as expected):

3 rows affected

Then

SELECT * FROM inventory;

Result:

item_id location_id
 123453      12-099
 123454      12-100
 123456      13-101
 123457      13-102
 123458      13-103

Et voilà! p.s. welcome to the forum! :-)

"A subquery has returned not exactly one row" error

3 Answers3