SQL Subquery return more than 1 row

Question

I need to set "dph" in this table "Strobjednavka", but i don´t know whats wrong there. Please help :).

Here is my SQL script:

UPDATE STRObjednavka as o SET dph = (
 SELECT dph FROM STRCena WHERE
  menuKodCode =
    (SELECT menuKodCode FROM STRMenu WHERE
      id = o.menuId
          )
  AND
  skupinaId =
    (SELECT stravGroupId FROM grups1 WHERE
      PKey =
        (SELECT SGroup FROM users1 WHERE
          PKey = o.userId
          )))
  WHERE o.price > 0 AND `date` > '2015-01-28 13:52:36' AND dph = 0;

SQL say : SQL error 1242: Subquery returns more than 1 row

This is not a duplicate of https://stackoverflow.com/questions/28171474/solution-to-subquery-returns-more-than-1-row-error, because here Scalar Subqueries in SET a are used. — dnoeth, Jul 19 '17 at 13:12
Check your subqueries again. If duplicates can't be removed (bad way), just add LIMIT 1 at end of you subqueries. Or chage equal condition to `IN`. — Vitaly, Jul 19 '17 at 13:41
yes i tried IN and Limit but that does not work for this script but ty :) — Martin Volek, Jul 19 '17 at 13:48

score 1 · Accepted Answer · answered Jul 19 '17 at 13:24

You can able to update with below script, but you need to check whether update is correct or not, If you give some sample data then it will be easy to track the problem.

UPDATE STRObjednavka as o SET dph = (
 SELECT max(dph) FROM STRCena WHERE
  menuKodCode =
    (SELECT max(menuKodCode) FROM STRMenu WHERE
      id = o.menuId
          )
  AND
  skupinaId =
    (SELECT max(stravGroupId) FROM grups1 WHERE
      PKey =
        (SELECT max(SGroup) FROM users1 WHERE
          PKey = o.userId
          )))
  WHERE o.price > 0 AND `date` > '2015-01-28 13:52:36' AND dph = 0;

score 1 · Answer 2 · answered Jul 19 '17 at 13:25

Unfortunately, MySQL doesn't allow you to LIMIT a subquery. Depending on your use case you can add MIN or MAX to your subqueries. Here it is with MINs in all the subqueries:

UPDATE STRObjednavka as o SET dph = (
 SELECT MIN(dph) FROM STRCena WHERE
  menuKodCode =
    (SELECT MIN(menuKodCode) FROM STRMenu WHERE
      id = o.menuId
          )
  AND
  skupinaId =
    (SELECT MIN(stravGroupId) FROM grups1 WHERE
      PKey =
        (SELECT MIN(SGroup) FROM users1 WHERE
          PKey = o.userId
          )))
  WHERE o.price > 0 AND `date` > '2015-01-28 13:52:36' AND dph = 0;

Although you really only need to add it to the subquery that's returning more than one row.

that works like other same answer in this topic, thank you so :) — Martin Volek, Jul 19 '17 at 13:43

score 0 · Answer 3 · answered Jul 19 '17 at 13:26

Your first problem is that you're writing '.... = (SELECT .... )'. Since you're using the equality operator, you're asking SQL to assign an entire column of values to a single cell. Change your equality operators before your subqueries to IN operators.

score 0 · Answer 4 · answered Jul 19 '17 at 13:29

You probably should use a different query pattern.

You have this sort of thing in your query, in several places.

WHERE menuKodCode =              /* !! might generate error 1242 */
     (SELECT menuKodCode FROM STRMenu WHERE id = o.menuId)

There's no guarantee that your inner query won't return more than one row, and when it does, MySQL throws error 1242.

SQL works wiith sets of values. If you used IN instead of =, your query would work.

WHERE ... menuKodCode IN
     (SELECT menuKodCode FROM STRMenu WHERE id = o.menuId)

But you should figure out whether that logic is correct. If I were you I'd do a whole bunch of SELECT operations to test it before doing UPDATE.

SQL Subquery return more than 1 row

4 Answers4

Linked