Subquery returns more than 1 row как исправить

Бывает так, что подзапрос одиночной строки возвращает более одной строки. В таком случае возникнет ошибка.

Для каждого магазина найдем одного сотрудника с должностью 'MANAGER'.

SELECT s.store_id,
       s.name,
       (SELECT e.employee_id
          FROM employee e
         WHERE e.store_id = s.store_id
           AND e.rank_id = 'MANAGER'
       ) AS employee_id
  FROM store s
 ORDER BY s.store_id

error: more than one row returned by a subquery used as an expression

Посмотрим, что там с данными не так?

SELECT e.store_id,
       e.rank_id,
       e.last_name || ' ' || e.first_name AS full_name 
  FROM employee e
 WHERE e.rank_id = 'MANAGER'
 ORDER BY e.store_id, e.last_name, e.first_name

В магазине 201 два менеджера, а в магазине 600 – три.

В такой ситуации есть два варианта развития событий. Мы можем решать неправильную задачу. Если в магазине может быть несколько менеджеров, то мы должны работать с массивом менеджеров. В таком случае может быть приемлемым получить одного из них, например, первого по алфавиту.

Возможно, менеджер в магазине должен быть только один. Просто кто-то не создал уникальный ключ, и пользователи создали несколько менеджеров в магазине. В таком случае, мы написали правильный запрос и необходимо избавляться от неправильных данных и дорабатывать структуру данных.

В любом случае, необходимо гарантировать, что в результате подзапроса будет возвращено не более одной строки. Для этого необходимо одно из:

• должен быть уникальный ключ, гарантирующий, что в результате подзапроса будет не более одной строки;
• использовать агрегатную функцию;
• использовать LIMIT 1 для ограничения количества строк.

Воспользуемся LIMIT 1:

SELECT s.store_id,
       s.name,
       (SELECT e.employee_id
          FROM employee e
         WHERE e.store_id = s.store_id
           AND e.rank_id = 'MANAGER'
         ORDER BY e.last_name,
                  e.first_name,
                  e.middle_name
         LIMIT 1
       ) AS employee_id
  FROM store s
 ORDER BY s.store_id

P.S. Если нам нужен список ФИО, то можно воспользоваться string_agg:

SELECT s.store_id,
       s.name,
       (SELECT string_agg (
                  e.last_name || ' ' || e.first_name, '; '
                  ORDER BY e.last_name,
                           e.first_name
               )
          FROM employee e
         WHERE e.store_id = s.store_id
           AND e.rank_id = 'MANAGER'
       ) AS employees
  FROM store s
 ORDER BY s.store_id

Таких функций в PostgreSQL довольно много, и они заслуживают отдельной темы.

Здравствуйте, не понимаю какое добавить еще условие, чтобы решить

DELIMITER //
CREATE TRIGGER Buget_Trigger
    AFTER INSERT
    ON Buget5
    FOR EACH ROW BEGIN
    if((select MaxCount from Buget5 where  (select max(dateee) where dateee<(now())) )<(select Buget from Buget5 where  (select max(dateee) where dateee<(now())))) then
      update Insects set Count=Count+20 where(select max(datee) where datee<(now()));
      update Mammals set Count=Count+20 where(select max(datee) where datee<(now()));
      update ColdBloodedness set Count=Count+20 where(select max(datee) where datee<(now()));
    end if;
    END //

insert into Mammals(mammals_id, id, typee, count, datee) VALUES (1,null,'',7,'2019-12-13');
insert into ColdBloodedness(ColdBloodedness_Id, id, typee,datee, count) VALUES (1,null,'','2019-12-13',10);
insert into Insects(insects_id, id, typee, count,datee) VALUES (1,null,'',5, '2019-12-13');
insert into Buget5(buget_id, id, buget, maxcount, dateee) VALUES (1, null,600, 200,'2019-12-13');
select * from Insects;
select * from ColdBloodedness;
select * from Mammals;

Но если сделаю второй раз, то ничего не сработает и будет ошибка 1242

insert into Buget5(buget_id, id, buget, maxcount, dateee) VALUES (2, null,600, 200,'2019-12-13');
select * from Insects;
select * from ColdBloodedness;
select * from Mammals;

There’re two error patterns related to ORA-01427 described in this post:

1. SELECT with Equal Operator
2. Job Failed by Trigger.

I will talk about them respectively in the following sections. In which, the first error pattern is very common and easy to solve. But the second one is not so obvious, you need more patience to solve it.

A. ORA-01427 in SELECT with Equal Operator

If SQL engine expects your subquery to return a single row, it may throw ORA-01427 when the subquery returns more than one row unexpectedly. For example:

SQL> select * from employees where department_id = (select department_id from departments where location_id = 1700);
select * from employees where department_id = (select department_id from departments where location_id = 1700)
                                               *
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

This is because the subquery in the SELECT statement returned more than one row for the predicate department_id, which does not comply with a singular value limited operator, the equal =. Consequently, the statement fails to continue and then throw ORA-01427 to notify developers.

Solutions

It’s just like that we wanted strictly only one item, but eventually it returned more than we expected.

1. Using IN Operator

Then what operator is used to prevent ORA-01427 in SELECT statement? In practice, SELECT should use IN operator instead of = (equal operator) in order to accommodate more than one row returned by the subquery.

SQL> select * from employees where department_id in (select department_id from departments where location_id = 1700);

A similar exception that relates to returned number mismatch is ORA-00913: too many values. But they have different error patterns.

More comparison conditions like ANY, SOME or ALL should also be helpful to accept more than one row in your statements so as to avoid ORA-01427 in subquery statement.

2. Using = (Equal) Operator

If you really want to use an equal operator to confine the scope of returned rows as one, you have to limit the number of rows returned of queries to only one row. That’s how we workaround it.

SQL> select * from employees where department_id = (select * from (select department_id from departments where location_id = 1700) where rownum = 1);

An order by clause is more appropriate in the above subquery so as to be close to your expectation.

select * from employees where department_id = (select * from (select department_id from departments where location_id = 1700 order by manager_id) where rownum = 1);

3. Do Not Use DISTINCT with Equal Operator

Adding one distinct keyword before column list cannot prevent ORA-01427, because the number of rows returned by the subquery is still unpredictable.

SQL> select * from employees where department_id = (select distinct department_id from departments where location_id = 1700);
select * from employees where department_id = (select distinct department_id from departments where location_id = 1700)
                                               *
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

No surprises, we saw ORA-01427 in SELECT statement once again.

B. ORA-01427 in Job Failed by Trigger

Same error ORA-01427 could accompany ORA-12012 in the alert log when one scheduled job failed to complete successfully.

ORA-12012: error on auto execute of job 10
ORA-01427: single-row subquery returns more than one row
ORA-06512: at line 21

Trigger Caused ORA-01427

According to this error pattern in the above, we saw ORA-01427 in a failed job with ORA-12012. We’d better check some triggers, especially logon and logoff ones to see if there’s any chances to block the job process accidentally. Noticeably, which is Job No. 10 in this case.

You should check the content of the job in the first place to see if there’re any chances to throw ORA-01427. Perhaps you should disable each trigger at a time in order to isolate and identify the cause. Or just turn them off temporarily.

Further reading: How to Kill Session in Logon Trigger

C. MySQL Subquery Returns More Than 1 Row

The same error pattern occurs in MySQL, incorrect number of rows from subquery will result ERROR 1241, let’s see its content:

ERROR 1242 (ER_SUBSELECT_NO_1_ROW)
SQLSTATE = 21000
Message = “Subquery returns more than 1 row”

This error is caused by the subquery that must return at most one row but returns multiple rows eventually.

mysql> select * from items where id = (select item_id from orders);

If the subquery returns just one row, the above query will work without errors. If it returns more than one row, we will see error 1242.

The solution is the same as we mentioned in the above sections. Beside IN operator, we can also use ANY, one of comparison conditions to fix the query, for example:

mysql> select * from items where id = any (select item_id from orders);

This is how comparison conditions work for MySQL.