Sql ошибка 1242 - TopOshibok.ru - решение и исправление самых разных ошибок

I got an error: #1242 — Subquery returns more than 1 row when i run this sql.

CREATE VIEW test 
AS 
  SELECT cc_name, 
         COUNT() AS total, 
         (SELECT COUNT(*) 
            FROM bed 
           WHERE respatient_id > 0 
        GROUP BY cc_name) AS occupied_beds, 
         (SELECT COUNT(*) 
            FROM bed 
           WHERE respatient_id IS NULL 
        GROUP BY cc_name) AS free_beds 
    FROM bed 
GROUP BY cc_name;

OMG Ponies

326k82 gold badges523 silver badges502 bronze badges

asked Oct 23, 2011 at 16:00

The problem is that your subselects are returning more than one value — IE:

SELECT ...
       (SELECT COUNT(*) 
          FROM bed 
         WHERE respatient_id IS NULL 
      GROUP BY cc_name) AS free_beds,
       ...

…will return a row for each cc_name, but SQL doesn’t support compacting the resultset for the subselect — hence the error.

Don’t need the subselects, this can be done using a single pass over the table using:

  SELECT b.cc_name, 
         COUNT(*) AS total, 
         SUM(CASE 
               WHEN b.respatient_id > 0 THEN 1 
               ELSE 0 
             END) AS occupied_beds, 
         SUM(CASE 
               WHEN b.respatient_id IS NULL THEN 1 
               ELSE 0 
             END) AS free_beds 
    FROM bed b
GROUP BY b.cc_name

answered Oct 23, 2011 at 16:05

OMG PoniesOMG Ponies

326k82 gold badges523 silver badges502 bronze badges

This is because your subqueries (the SELECT bits that are inside parentheses) are returning multiple rows for each outer row. The problem is with the GROUP BY; if you want to use subqueries for this, then you need to correlate them to the outer query, by specifying that they refer to the same cc_name as the outer query:

CREATE VIEW test 
AS 
  SELECT cc_name, 
         COUNT()             AS total, 
         (SELECT COUNT() 
          FROM   bed 
          WHERE  cc_name = bed_outer.cc_name
          AND    respatient_id > 0) AS occupied_beds, 
         (SELECT COUNT(*) 
          FROM   bed 
          WHERE  cc_name = bed_outer.cc_name
          WHERE  respatient_id IS NULL) AS free_beds 
  FROM   bed AS bed_outer
  GROUP  BY cc_name;

(See http://en.wikipedia.org/wiki/Correlated_subquery for information about correlated subqueries.)

But, as OMG Ponies and a1ex07 say, you don’t actually need to use subqueries for this if you don’t want to.

answered Oct 23, 2011 at 16:11

ruakhruakh

176k26 gold badges273 silver badges307 bronze badges

Your subqueries return more than 1 row. I think you you need something like :

 SELECT COUNT(*) AS total, 
 COUNT(CASE WHEN respatient_id > 0 THEN 1 END) AS occupied_beds,
 COUNT(CASE WHEN respatient_id IS NULL THEN 1 END) AS free_beds          
 FROM   bed 
 GROUP  BY cc_name

You can also try to use WITH ROLLUP + pivoting (mostly for learning purposes, it’s a much longer query ) :

SELECT cc_name, 
MAX(CASE 
 WHEN num_1 = 1 THEN tot_num END) AS free_beds,

MAX(CASE 
 WHEN num_1 = 2 THEN tot_num END) AS occupied_beds,

MAX(CASE 
 WHEN num_1 = IS NULL THEN tot_num END) AS total

FROM
(SELECT cc_name, CASE 
WHEN respatient_id > 0 THEN 1
WHEN respatient_id IS NULL THEN 2
ELSE 3 END as num_1,
COUNT(*) as tot_num
FROM  bed
WHERE 
CASE 
WHEN respatient_id > 0 THEN 1
WHEN respatient_id IS NULL THEN 2
ELSE 3 END != 3
GROUP BY cc_name,
num_1 WITH ROLLUP)A
GROUP BY cc_name

answered Oct 23, 2011 at 16:06

a1ex07a1ex07

36.9k12 gold badges90 silver badges103 bronze badges

SELECT COUNT() 
          FROM   bed 
          WHERE  respatient_id > 0 
          GROUP  BY cc_name

You need to remove the group-by in the sub query, so possibly something like

SELECT COUNT(*) 
          FROM   bed 
          WHERE  respatient_id > 0

or possibly — depending on what your application logic is….

SELECT COUNT(*) from (
          select count(*),cc_name FROM   bed 
          WHERE  respatient_id > 0 
          GROUP  BY cc_name) filterview

answered Oct 23, 2011 at 16:02

SorenSoren

14.4k4 gold badges41 silver badges67 bronze badges

Источник

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

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;

Источник

I am trying to make a select statement that selects the image names from a MySQL database.
The table is called — pictures_archive. I am also trying to select these pictures depending on the category they have. The code is:

SELECT pictures_archive_filename FROM pictures_archive 
WHERE pictures_archive_id = (SELECT pictures_archive_id 
FROM pictures_archive_category WHERE pictures_category_id = 9)

It gives me an «#1242 — Subquery returns more than 1 row» error. I can see why, but can’t figure it out how to do it.

Marc Alff

8,22733 silver badges59 bronze badges

asked Sep 26, 2012 at 8:30

Since your subquery can return multiple values, IN should fit in youy where clause.

SELECT pictures_archive_filename 
FROM pictures_archive 
WHERE pictures_archive_id IN 
(
   SELECT pictures_archive_id 
   FROM pictures_archive_category 
   WHERE pictures_category_id = 9
)

an alternative for this is to join both tables which is more efficient.

SELECT  pictures_archive_filename 
FROM    pictures_archive a 
        INNER JOIN pictures_archive_category b
            ON a.pictures_archive_id = b.pictures_archive_id
WHERE   b.pictures_category_id = 9

answered Sep 26, 2012 at 8:32

John WooJohn Woo

259k69 gold badges498 silver badges492 bronze badges

Use IN instead of equal (=)

SELECT pictures_archive_filename FROM pictures_archive 
WHERE pictures_archive_id IN (SELECT pictures_archive_id 
FROM pictures_archive_category WHERE pictures_category_id = 9)

OR if possible use a JOIN between 2 tables

answered Sep 26, 2012 at 8:31

PrasannaPrasanna

4,5932 gold badges22 silver badges29 bronze badges

SELECT pictures_archive_filename FROM pictures_archive 
WHERE pictures_archive_id = (SELECT pictures_archive_id 
FROM pictures_archive_category WHERE pictures_category_id = 9 LIMIT 1)

answered Sep 26, 2012 at 8:33

PermGenErrorPermGenError

46k8 gold badges87 silver badges106 bronze badges

SELECT p.pictures_archive_filename FROM 
pictures_archive p inner join pictures_archive_category pc 
on p.pictures_archive_id = pc.pictures_archive_id 
where pc.pictures_category_id=9

answered Sep 26, 2012 at 8:35

AnandPhadkeAnandPhadke

13.2k5 gold badges26 silver badges33 bronze badges

Источник

So I’ve just started learning MySQL with a few exercises and I’m currently stuck at these 2 problems. Since this is technically an exercise I’d really appreciate hints instead of full solutions. Here are the 2 tables first:

CREATE TABLE LINE (
INV_NUMBER int,
LINE_NUMBER int,
P_CODE varchar(10),
LINE_UNITS float(8),
LINE_PRICE float(8),
LINE_TOTAL float(8)
);
INSERT INTO LINE VALUES('1001','1','13-Q2/P2','1','14.99','14.99');
INSERT INTO LINE VALUES('1001','2','23109-HB','1','9.95','9.95');
INSERT INTO LINE VALUES('1002','1','54778-2T','2','4.99','9.98');
INSERT INTO LINE VALUES('1003','1','2238/QPD','1','38.95','38.95');
INSERT INTO LINE VALUES('1003','2','1546-QQ2','1','39.95','39.95');
INSERT INTO LINE VALUES('1003','3','13-Q2/P2','5','14.99','74.95');
INSERT INTO LINE VALUES('1004','1','54778-2T','3','4.99','14.97');
INSERT INTO LINE VALUES('1004','2','23109-HB','2','9.95','19.90');
INSERT INTO LINE VALUES('1005','1','PVC23DRT','12','5.87','70.44');
INSERT INTO LINE VALUES('1006','1','SM-18277','3','6.99','20.97');
INSERT INTO LINE VALUES('1006','2','2232/QTY','1','109.92','109.92');
INSERT INTO LINE VALUES('1006','3','23109-HB','1','9.95','9.95');
INSERT INTO LINE VALUES('1006','4','89-WRE-Q','1','256.99','256.99');
INSERT INTO LINE VALUES('1007','1','13-Q2/P2','2','14.99','29.98');
INSERT INTO LINE VALUES('1007','2','54778-2T','1','4.99','4.99');
INSERT INTO LINE VALUES('1008','1','PVC23DRT','5','5.87','29.35');
INSERT INTO LINE VALUES('1008','2','WR3/TT3','3','119.95','359.85');
INSERT INTO LINE VALUES('1008','3','23109-HB','1','9.95','9.95');

CREATE TABLE PRODUCT (
P_CODE varchar(10),
P_DESCRIPT varchar(35),
P_INDATE date,
P_QOH int,
P_MIN int,
P_PRICE float(8),
P_DISCOUNT float(8),
V_CODE int
);
INSERT INTO PRODUCT VALUES('11QER/31','Power painter, 15 psi., 3- 
nozzle','2015-11-03','8','5','109.99','0','25595');
INSERT INTO PRODUCT VALUES('13-Q2/P2','7.25-in. pwr. saw blade','2015-12- 
13','32','15','14.99','0.05',NULL);
INSERT INTO PRODUCT VALUES('14-Q1/L3','9.00-in. pwr. saw blade','2015-11- 
13','18','12','17.49','0','21344');
INSERT INTO PRODUCT VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50','2016-01- 
15','15','8','39.95','0','23119');
INSERT INTO PRODUCT VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50','2016-01- 
15','23','5','43.99','0','23119');
INSERT INTO PRODUCT VALUES('2232/QTY','B&D jigsaw, 12-in. blade','2015-12- 
30','8','5','109.92','0.05','24288');
INSERT INTO PRODUCT VALUES('2232/QWE','B&D jigsaw, 8-in. blade','2015-12- 
24','6','5','99.87','0.05','24288');
INSERT INTO PRODUCT VALUES('2238/QPD','B&D cordless drill, 1/2-in.','2016- 
01-20','12','5','38.95','0.05','25595');
INSERT INTO PRODUCT VALUES('23109-HB','Claw hammer','2016-01- 
12','23','10','9.95','0.1','21225');
INSERT INTO PRODUCT VALUES('23114-AA','Sledge hammer, 12 lb.','2016-01- 
2','8','5','14.40','0.05',NULL);
INSERT INTO PRODUCT VALUES('54778-2T','Rat-tail file, 1/8-in. fine','2015- 
12-15','43','20','4.99','0','21344');
INSERT INTO PRODUCT VALUES('89-WRE-Q','Hicut chain saw, 16 in.','2016-02- 
17','11','5','256.99','0.05','24288');
INSERT INTO PRODUCT VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft','2016-02- 
27','188','75','5.87','0','24004');
INSERT INTO PRODUCT VALUES('SM-18277','1.25-in. metal screw, ''25','2016-03- 
01','172','75','6.99','0','21225');
INSERT INTO PRODUCT VALUES('SW-23116','2.5-in. wd. screw, 50','2016-02- 
14','237','100','8.45','0','21231');
INSERT INTO PRODUCT VALUES('WR3/TT3','Steel matting, 4''x8''x1/6", .5" 
mesh','2016-01-27','18','5','119.95','0.1','25595');

PKs are line.INV_NUMBER, line.LINE_NUMBER, product.P_CODE and FKs are line.P_CODE and line.V_CODE

So the first question is to list all product sales that are greater than the average units sold for that product and add a correlated in-line sub-query to list the average units sold per product.
If I understood correctly this means that I need to sum the line.LINE_UNITS group by P_CODE then compare it to the average units sold but I keep getting the same error that says sub query returns more than 1 row. Here’s my code:

select  P_CODE,
        LINE_UNITS, 
        ( SELECT  AVG(line.LINE_UNITS) ) as ‘Unit_Average’
    from  line
    where  
        ( SELECT  sum(line.LINE_UNITS)
            from  line
            group by  P_CODE ) >
        ( SELECT  AVG(line.LINE_UNITS) );

I think I need to do a join instead and I’d really appreciate some hints.

For the second question I am supposed to list the difference between each product’s prices and the average product price. I managed to type up a few lines but I couldn’t get it to execute for all rows of the product table. I’m thinking of using over() but im having quite a lot of syntax errors. Here’s my code:

select  P_CODE, P_PRICE, 
        ( SELECT  avg(product.P_PRICE) ) as 'Average',
        if(product.P_PRICE <= 
              ( SELECT  avg(product.P_PRICE) ), 
              ( SELECT  avg(product.P_PRICE) ) - product.P_PRICE,
              product.P_PRICE - ( SELECT  avg(product.P_PRICE) )
        ) as 'Difference'
    from  product;

Thanks guys!

Источник

у меня есть две таблицы в БД со следующей структурой:

Таблица 1: 3 строки — category_id, product_id и position

таблица 2: 3 строки — category_id, product_id и position

я пытаюсь установить положение таблицы 1 в положение таблицы 2, где категория и идентификатор продукта совпадают с таблицами.

ниже SQL, я пытался сделать это, но возвращает ошибку MySQL 1242 — подзапрос возвращает более 1 строки

UPDATE table1
SET position = (
SELECT position
FROM table2
WHERE table1.product_id = table2.product_id AND table1.category_id = table2.category_id
)

Решение

Решение очень простое и может быть сделано в два простых шага. Первый шаг — это предварительный просмотр того, что будет изменено, чтобы избежать уничтожения данных. Это можно пропустить, если вы уверены в своем WHERE пункт.

Шаг 1: предварительный просмотр изменений

Объедините таблицы, используя поля, которые вы хотите сопоставить, выберите все для визуальной проверки соответствия.

SELECT t1.*, t2.*
FROM table1 t1
INNER JOIN table2 t2
ON t1.category_id = t2.category_id
AND t1.product_id = t2.product_id

Вы также можете добавить WHERE предложение, если только некоторые строки должны быть изменены.

Шаг 2: сделать актуальное обновление

Заменить SELECT пункт и FROM ключевое слово с UPDATE, добавить SET пункт, где это принадлежит. Держать WHERE пункт:

UPDATE table1 t1
INNER JOIN table2 t2
ON t1.category_id = t2.category_id
AND t1.product_id = t2.product_id
SET t1.position = t2.position

Это все.

Технические соображения

Индексы столбцов, используемых на JOIN предложение в обеих таблицах является обязательным, если в таблицах более нескольких сотен строк. Если запрос не имеет WHERE условия, то MySQL будет использовать индексы только для самой большой таблицы. Индексы полей, используемых на WHERE условие ускорит запрос. Prepend EXPLAIN к SELECT запрос, чтобы проверить план выполнения и решить, какие индексы вам нужны.

Можете добавить SORT BY а также LIMIT для дальнейшего сокращения набора измененных строк, используя критерии, которые не могут быть достигнуты с помощью WHERE (например, только самые последние / самые старые 100 строк и т. д.). Поместите их на SELECT сначала запрос, чтобы проверить результат, а затем изменить SELECT в UPDATE как описано.
Конечно, индексы на столбцах, используемых на SORT BY пункт является обязательным.

Другие решения

Вы можете запустить этот запрос, чтобы увидеть, что происходит:

SELECT product_id, category_id, count(*), min(position), max(position)
FROM table2
GROUP BY product_id, category_id
HAVING COUNT(*) > 1;

Это даст вам список product_id, category_id пары, которые появляются несколько раз в table2, Тогда вы можете решить, что делать. Вы хотите произвольное значение position? Является ли значение position всегда одно и то же? Вам нужно починить стол?

Достаточно легко решить конкретную проблему с помощью limit 1 или функция агрегации. Тем не менее, вам может понадобиться исправить данные в таблице. Исправление выглядит так:

UPDATE table1 t1
SET t1.position = (SELECT t2.position
FROM table2  t2
WHERE t2.product_id = t1.product_id AND t2.category_id = t1.category_id
LIMIT 1
);

Источник

Решение

Шаг 1: предварительный просмотр изменений

Шаг 2: сделать актуальное обновление

Технические соображения

Другие решения

Интересное по теме: