Как исправить «subquery returns more than 1 row» в «after update» триггере?

Question

[telop]

У меня есть две таблицы, test1 иtest2, данные в test2 зависят от данных в test1 и я создал триггер after update который после изменения данных вtest1 обновляет test2
Все работает если у меня в таблице всего 1 строка, тогда триггер срабатывает, но если строк больше одной, тогда выдает ошибку subquery returns more than 1 row
Вот сам триггер:

CREATE DEFINER=`root`@`localhost` TRIGGER `afupd_yzv2` AFTER UPDATE ON `test1` FOR EACH ROW BEGIN 
    UPDATE `database`.`test2` SET `value1` = (SELECT value1 FROM test1) * 2 WHERE (`id` = (SELECT id FROM test1));
END

Помогите пожалуйста, что можно сделать чтобы я мог работать с множеством строк, а не с одной?

Answer 1

[Михаил]

А что вы пытаетесь тут сделать? Вот конкретно этот подзапрос (SELECT id FROM test1) он для чего? Вы выбираете все записи из таблицы test1, что, как бы, очень подозрительно. А если там будет миллион записей?

У вас в триггере есть переменные NEW и OLD, указывающие на обновленную запись в таблице test1 - вот и используйте их, чтобы определить какие записи в таблице test2 вам нужно обновить.

Comments

[telop]

Вы имеете ввиду по типу (SELECT new.value1 FROM test1)
И WHERE (`id` = (SELECT new.id FROM test1))?

[Михаил]

telop, давайте вместе откроем документацию: https://dev.mysql.com/doc/refman/8.0/en/trigger-sy...

И найдем там пример вот такого триггера:

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  b4 INT DEFAULT 0
);

delimiter |

--Вот этого вот самого триггера
CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW
  BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END;
|

delimiter ;

INSERT INTO test3 (a3) VALUES
  (NULL), (NULL), (NULL), (NULL), (NULL),
  (NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES
  (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

Answer 2

[NiceDay]

у вас в UPDATE входит два подзапроса. какой-то из них возвращает больше одной строки, что не применимо в данной ситуации.

например, вот этот блок (SELECT value1 FROM test1) -
в случае, если ваша таблица test1 содержит больше одной строки, этот запрос вернет их все.
если вам нужно получить новое значение столбца обновляемой таблицы, вы можете, как заметил комментатор выше, использовать NEW или OLD:

UPDATE `database`.`test2` SET `value1` = NEW.value1 * 2...

аналогично предыдущему,
WHERE (`id` = (SELECT id FROM test1));
вернет столько строк с одним столбцом id, сколько существует в таблице test1, что не допустимо для оператора равенства.
если вам нужно обновить строку в таблице test2, id которой такой же, как и id у обновляемой записи в test1, то следует использовать NEW.id и ваш запрос приобретет следующий вид:

CREATE DEFINER=`root`@`localhost` TRIGGER `afupd_yzv2` AFTER UPDATE ON `test1` FOR EACH ROW BEGIN 
    UPDATE `database`.`test2` SET `value1` = NEW.value1 * 2 WHERE `id` = NEW.id
END