how to fix: Error Code: 1242. Subquery returns more than 1 row

Question

This works fine:

UPDATE users 
        SET group_id = (SELECT Prov_Current_Address_City FROM users WHERE scope IS NULL AND health_facility_id = 10) 
WHERE scope IN ('MHO', 'Mayor', 'Doctor') AND health_facility_id = 10;

With this i get an ERROR:

UPDATE users 
        SET group_id = (SELECT Prov_Current_Address_City FROM users WHERE scope IS NULL AND health_facility_id = (SELECT Prov_Current_Address_City FROM users WHERE scope IS NULL)) 
WHERE scope IN ('MHO', 'Mayor', 'Doctor') AND health_facility_id = (SELECT health_facility_id FROM users WHERE scope IS NULL);

but this

(SELECT Prov_Current_Address_City FROM users WHERE scope IS NULL)

query is returning many data. What I want to achieve is to update multiple rows in Column group_id. see this image https://i.imgur.com/aNSsWie.png

Please explain what you want to accomplish. Sample data and desired results would all help. — Gordon Linoff, Sep 01 '19 at 15:08

nbk · Answer 1 · 2019-09-02T11:19:29.543

0

If you don't have any more criteria to limit the citys. do something like this.

UPDATE users 
SET group_id = (SELECT Prov_Current_Address_City 
                FROM users 
                WHERE scope IS NULL 
                 AND health_facility_id = (SELECT Prov_Current_Address_City 
                                           FROM users 
                                          WHERE scope IS NULL
                                          ORDER BY Prov_Current_Address_City
                                          Limit 1)
                ORDER BY Prov_Current_Address_City
                Limit 1) 
WHERE scope IN ('MHO', 'Mayor', 'Doctor') 
AND health_facility_id = (SELECT health_facility_id 
                          FROM users 
                          WHERE scope IS NULL
                          ORDER BY health_facility_id 
                          Limit 1);

edited Sep 02 '19 at 11:19

answered Sep 01 '19 at 15:07

nbk

45,398
8
30
47

Update to a more or less random value, instead of correcting the bug? – jarlh Sep 01 '19 at 17:37
How to select by random ordering look here https://stackoverflow.com/questions/1823306/mysql-alternatives-to-order-by-rand – nbk Sep 01 '19 at 17:49
i tried your answer but still same result – Sam N Den Sep 02 '19 at 01:38
@Claude try this new approach, now all only select querys return only 1 row., if this also doesn't work you have to build in dbfiddle.uk an example database, because i believe you can eventually group the result. – nbk Sep 02 '19 at 11:23

score 0 · Answer 2 · answered Sep 02 '19 at 02:56

Just change = to IN when a subquery returns more than one row. If you were not expecting the subqueries to return more than one row then you need to fix your original query.

UPDATE users 
SET group_id =
    (
    SELECT Prov_Current_Address_City
    FROM users
    WHERE scope IS NULL
    AND health_facility_id IN
        (
        SELECT Prov_Current_Address_City
        FROM users
        WHERE scope IS NULL
        )
    ) 
WHERE scope IN ('MHO', 'Mayor', 'Doctor')
AND health_facility_id IN
    (
    SELECT health_facility_id
    FROM users
    WHERE scope IS NULL
    );

how to fix: Error Code: 1242. Subquery returns more than 1 row

2 Answers2