How to fix "subquery returns more than 1 row" in "after update" trigger?

Question

MySQL

I have two tables, test1and test2, the data in test2depends on the data in test1and I created a trigger that, after changing the data in, updates Everything works if I have only 1 row in the table, then the trigger fires, but if there are more than one row, then it gives out Here is the trigger itself :after updatetest1test2
ошибку 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

Please help me, what can I do so that I can work with many lines, and not just one?

Answer 1

What are you trying to do here? What exactly is this subquery (SELECT id FROM test1)for? You are selecting all records from table test1, which is very suspicious. What if there are a million records?
You have NEW and OLD variables in your trigger pointing to the updated record in table test1 - so use them to determine which records in table test2 you need to update.