Error 1242(21000) after creating following trigger and performing delete operation I want to create a trigger which copies the record from maintable to undostack before deleting it in maintable and undostack table acts a stack of 5 records space and this trigger performs a push operation.
This is my trigger
CREATE TRIGGER undo_op
BEFORE DELETE ON maintable FOR EACH ROW
BEGIN

When undostack is not full
IF (SELECT COUNT(*) FROM (SELECT * FROM undostack)u)&lt;5 THEN
INSERT INTO undostack VALUES(((SELECT COUNT(*) FROM undostack)+1),
(SELECT id FROM maintable WHERE id=OLD.id),
(SELECT descp FROM maintable WHERE descp=OLD.descp),
(SELECT cat FROM maintable WHERE cat=OLD.cat),
(SELECT dat FROM maintable WHERE dat=OLD.dat),
(SELECT amt FROM maintable WHERE amt=OLD.amt));

When undostack is full and has to delete the oldest record entered by updating. here undo_no acts as pointer.
ELSE

1&lt;-2
UPDATE undostack SET
id=(SELECT id FROM (SELECT * FROM undostack)u WHERE undo_no=2),
descp=(SELECT descp FROM (SELECT * FROM undostack)u WHERE undo_no=2),
cat=(SELECT cat FROM (SELECT * FROM undostack)u WHERE undo_no=2),
dat=(SELECT dat FROM (SELECT * FROM undostack)u WHERE undo_no=2),
amt=(SELECT amt FROM (SELECT * FROM undostack)u WHERE undo_no=2)
WHERE undo_no=1;

2&lt;-3
UPDATE undostack SET
id=(SELECT id FROM (SELECT * FROM undostack)u WHERE undo_no=3),
descp=(SELECT descp FROM (SELECT * FROM undostack)u WHERE undo_no=3),
cat=(SELECT cat FROM (SELECT * FROM undostack)u WHERE undo_no=3),
dat=(SELECT dat FROM (SELECT * FROM undostack)u WHERE undo_no=3),
amt=(SELECT amt FROM (SELECT * FROM undostack)u WHERE undo_no=3)
WHERE undo_no=2;

3&lt;-4
UPDATE undostack SET
id=(SELECT id FROM (SELECT * FROM undostack)u WHERE undo_no=4),
descp=(SELECT descp FROM (SELECT * FROM undostack)u WHERE undo_no=4),
cat=(SELECT cat FROM (SELECT * FROM undostack)u WHERE undo_no=4),
dat=(SELECT dat FROM (SELECT * FROM undostack)u WHERE undo_no=4),
amt=(SELECT amt FROM (SELECT * FROM undostack)u WHERE undo_no=4)
WHERE undo_no=3;

4&lt;-5
UPDATE undostack SET
id=(SELECT id FROM (SELECT * FROM undostack)u WHERE undo_no=5),
descp=(SELECT descp FROM (SELECT * FROM undostack)u WHERE undo_no=5),
cat=(SELECT cat FROM (SELECT * FROM undostack)u WHERE undo_no=5),
dat=(SELECT dat FROM (SELECT * FROM undostack)u WHERE undo_no=5),
amt=(SELECT amt FROM (SELECT * FROM undostack)u WHERE undo_no=5)
WHERE undo_no=4;

5&lt;-update with the values which are to be deleted from maintable
UPDATE undostack SET
id=(SELECT id FROM maintable WHERE id=OLD.id),
descp=(SELECT descp FROM maintable WHERE descp=OLD.descp),
cat=(SELECT id FROM maintable WHERE cat=OLD.cat),
dat=(SELECT id FROM maintable WHERE dat=OLD.dat),
amt=(SELECT amt FROM maintable WHERE amt=OLD.amt)
WHERE undo_no=5;
END IF;
END

The trigger has no errors
but when I execute
DELETE FROM maintable WHERE id=10;

There is following error
ERROR 1242 (21000):Subquery returns more than 1 row
Please help me with this error
I'm using MySQL 5.7.3
The easiest way to do this, is(for example undo_no 1)
UPDATE undostack SET
id=(SELECT id FROM (SELECT * FROM undostack) u WHERE undo_no=2),
descp=(SELECT descp FROM (SELECT * FROM undostack) u WHERE undo_no=2),
cat=(SELECT cat FROM (SELECT * FROM undostack) u WHERE undo_no=2),
dat=(SELECT dat FROM (SELECT * FROM undostack) u WHERE undo_no=2),
amt=(SELECT amt FROM (SELECT * FROM undostack) u WHERE undo_no=2)
WHERE undo_no=1;

By using (SELECT * FROM undostack) u Mysql generates a table which it uses to select your data.
usully i would say make this better in your pplication code, but five rows is very samll