MySQL Subquery Returns More Than One Row

Solution to subquery returns more than 1 row error

= can be used when the subquery returns only 1 value.

When subquery returns more than 1 value, you will have to use IN:

select * 
from table
where id IN (multiple row query);

For example:

SELECT *
FROM Students
WHERE Marks = (SELECT MAX(Marks) FROM Students)   --Subquery returns only 1 value

SELECT *
FROM Students
WHERE Marks IN 
      (SELECT Marks 
       FROM Students 
       ORDER BY Marks DESC
       LIMIT 10)                       --Subquery returns 10 values

How to overcome MySQL 'Subquery returns more than 1 row' error and select all the relevant records

GROUP_CONCAT with SEPARATOR and simplify your query a bit:

SELECT users.name AS name,
(SELECT GROUP_CONCAT(email_address.email_address SEPARATOR ', ')
FROM email_address
WHERE email_address.user_id = users.id) AS email
FROM users

Reference: https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

Subquery returns more than 1 row when aggregation is used

Edit: Based on table structure and output required, I updated the query and removed unwanted where and left join.

I don't know what you're trying to do. But based on my understanding I corrected your query:

SELECT
    employee.employee_id AS employee_id,
    profit_left.employee_department
    profit_left.company_profit
FROM
employee
JOIN (
    SELECT
        company.employee_id,
        company.employee_department,
        SUM(company.company_profit_left) as company_profit
    FROM company
    GROUP BY company.employee_id,company.employee_department
) AS profit_left on employee.employee_id=profit_left.employee_id

Subquery returns more than 1 row in select statement

Use a join:

SELECT t1.orderDate,
       t1.status,
       t2.prices
FROM orders t1
INNER JOIN
(
    SELECT orderNumber,
           SUM(quantityOrdered * priceEach) AS prices
    FROM orderdetails
    GROUP BY orderNumber
) t2
    ON t1.orderNumber = t2.orderNumber

Subquery returns more than 1 row. Using SQL select to update a different tables results

In MySQL, a value of zero appearing in a WHERE clause means false.

So, UPDATE something SET col=val WHERE (SELECT colx FROM sometable) has the potential to be a valid query. If the inner SELECT gets just one row, and its colx column has the value 0, the update won't do anything. If the colx column has a nonzero value the query means UPDATE something SET col=val WHERE true. Accordingly, every row in sometable will be updated. I doubt that's what you want.

If the inner SELECT happens to return more than one row, the query isn't valid. You'll get the error 1242 you actually received.

(This business of interpreting numbers as Boolean values causes MySQL to accept some otherwise dodgy query syntax, like the syntax in your question.)

I guess you want to retrieve the job_queue_id values for the row or rows you actually want to update. So try something like this.

update jobqueue
   set jobqueue.`ignore`= '1'
 where jobqueue.job_queue_id IN (SELECT JobQueue_job_queue_id
                                   FROM mdtinfo
                                  WHERE product_name  = 'Example')

I guessed you have a column jobqueue.job_queue_id. You didn't tell us what columns you have in jobqueue.

Only in my server returns Subquery returns more than 1 row

It means your server has different data stored in database: there are multiple rows with same meta_key (if error is related with queries with meta_key, if not - adjust column name)

Add GROUP BY meta_key or LIMIT 1 to your sub-queries. E.g.

SELECT SQL_CALC_FOUND_ROWS p.*, 
    (SELECT meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND meta_key = 'localidad' LIMIT 1) AS 'localidad', 
    ...

MySQL: Subquery returns more than 1 row when selecting results of multiple subqueries

SQL queries do not return nested result sets; so an expression (such as a subquery) used in a SELECT clause cannot have multiple values, as that would "nest" it's values. You more likely just need to use conditional aggregation, like so:

SELECT a.id, u.id, a.name, u.last_name
    , COUNT(CASE WHEN m.date_start BETWEEN CURDATE() AND DATE_ADD(CURDATE(),INTERVAL 30 DAY) THEN 1 ELSE NULL END) AS `Meetings 1-30 days`
    , COUNT(CASE WHEN m.date_start BETWEEN DATE_ADD(CURDATE(),INTERVAL 31 DAY) AND DATE_ADD(CURDATE(),INTERVAL 60 DAY) THEN 1 ELSE NULL END) AS `Meetings 31-60 days`
    , COUNT(CASE WHEN  THEN 1 ELSE NULL END) AS 
FROM accounts AS a
JOIN meetings AS m ON a.id = m.account_id
JOIN users AS u ON m.assigned_user_id = u.id
WHERE m.status = 'Planned' AND m.deleted = 0
  AND m.date_start BETWEEN CURDATE() AND DATE_ADD(CURDATE(),INTERVAL 60 DAY)
GROUP BY a.id, u.id, a.name, u.last_name
;

Notes: ELSE NULL is technically automatic, and can be omitted; it is just there for clarity. Aggregate functions, such as COUNT, ignore NULL values; the only time null values affect such functions is when they encounter only null values (in which case their results are null).

Sidenote: You could have continued with your query in a form similar to what you originally had; if you included the grouping fields in the subqueries' results, the subqueries could have been joined together (but that would have been a lot of redundant joining of accounts, meetings, and users).

What does the error single-row subquery returns more than one row means and how to fix it?

As @Halim Saad-Rached mentions in their answer, SELECT SCORE FROM MOVIE is returning multiple rows and you are then trying to compare each row in the outer query to multiple rows in the inner query.

I'm trying to query for all movies that have a higher score than a randomly selected movie score.

You need to find a single random movie rather than all movies.

From Oracle 12, you can use:

SELECT TITLE
FROM   MOVIE
WHERE  SCORE > (SELECT SCORE
                FROM   MOVIE
                ORDER BY DBMS_RANDOM.VALUE()
                FETCH FIRST ROW ONLY
               );

Or, in earlier versions:

SELECT TITLE
FROM   MOVIE
WHERE  SCORE > (SELECT score
                FROM   (
                  SELECT SCORE
                  FROM   MOVIE
                  ORDER BY DBMS_RANDOM.VALUE()
                )
                WHERE  ROWNUM = 1
               );

or, using analytic functions to only query the table once:

SELECT title
FROM   (
  SELECT title,
         score,
         MIN(score)
           KEEP (DENSE_RANK FIRST ORDER BY DBMS_RANDOM.VALUE(), ROWNUM)
           OVER ()
           AS random_score
  FROM   MOVIE
)
WHERE  score > random_score;

db<>fiddle here

---

Related Topics