Ошибка 1242 mysql

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

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;

If you are working with MySQL, you might come across a problem where a subquery returns more than one row. This can happen when you use a subquery in a WHERE or HAVING clause, and that subquery returns multiple rows. In this guide, we will explain what causes this problem and how you can fix it with code examples.

Understanding the Problem

When you use a subquery in a WHERE or HAVING clause, MySQL expects the subquery to return only one row. However, if your subquery returns multiple rows, MySQL will throw an error. This error message will look something like this:

ERROR 1242 (21000): Subquery returns more than 1 row

This problem can be caused by a variety of factors, such as using an incorrect JOIN condition or not using a LIMIT clause in your subquery. Whatever the cause, the solution is usually to modify your SQL query to return only one row in the subquery.

Fixing the Problem

Here are some examples of how you can fix the problem of a MySQL subquery returning more than one row:

Example 1: Use the IN Operator

One way to fix the problem is to use the IN operator instead of the = operator. For example, instead of writing:

SELECT *
FROM table1
WHERE column1 = (SELECT column1 FROM table2 WHERE condition);

You can write:

SELECT *
FROM table1
WHERE column1 IN (SELECT column1 FROM table2 WHERE condition);

This will return all rows from table1 where column1 matches any of the values returned by the subquery.

Example 2: Use the EXISTS Operator

Another way to fix the problem is to use the EXISTS operator. For example, instead of writing:

SELECT *
FROM table1
WHERE column1 = (SELECT column1 FROM table2 WHERE condition);

You can write:

SELECT *
FROM table1
WHERE EXISTS (SELECT column1 FROM table2 WHERE condition AND table1.column1 = table2.column1);

This will return all rows from table1 where there exists at least one row in table2 that matches the condition.

Example 3: Use the LIMIT Clause

If you know that your subquery should only return one row, you can use the LIMIT clause to limit the number of rows returned. For example:

SELECT *
FROM table1
WHERE column1 = (SELECT column1 FROM table2 WHERE condition LIMIT 1);

This will return all rows from table1 where column1 matches the value returned by the subquery, but will only return the first row of the subquery.

Conclusion

If you are getting an error message saying that your MySQL subquery returns more than one row, don’t panic! This is a common problem that can be easily fixed. By using the IN and EXISTS operators or the LIMIT clause, you can modify your SQL query to return only one row in the subquery. We hope this guide has been helpful in solving your MySQL subquery problem.

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

Решение

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

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

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

SELECT product_id, category_id, count(*), min(position), max(position)
FROM table2
GROUP BY product_id, category_id
HAVING COUNT(*) > 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
);