CODEWITHSUNDEEP
mysqlsqldatabasetriggers
jampez77
jampez77

Reputation: 5241

Database trigger select multiple rows and insert into table

Knew to using triggers for databases. I'm trying to select 10 random records from one table and insert them all into another one while keeping the last insert ID. I'm currently using:

CREATE TRIGGER `Deal White Cards` AFTER INSERT ON `u`
FOR EACH ROW 
INSERT INTO dh (cid, uid) VALUES 
(
 (SELECT `id` FROM `c` WHERE `colour` = 'w' ORDER BY RAND() LIMIT 10),
 NEW.id
)

As it stands at the moment I am just receiving an error (Subquery returns more than 1 row)

Upvotes: 0

Views: 2150

Answers (1)

Mikajlo8
Mikajlo8

Reputation: 81

You are receiving this error because this part of the subquery returns more than one row:

SELECT `id` FROM `c` WHERE `colour` = 'w' ORDER BY RAND() LIMIT 10

and You are trying to put it into a single row.

The solution to that is:

CREATE TRIGGER `Deal White Cards` AFTER INSERT ON `u`
FOR EACH ROW 
INSERT INTO dh (cid, uid)  
SELECT `id`, NEW.id FROM `c` WHERE `colour` = 'w' ORDER BY RAND() LIMIT 10;

Hope, it is what You have meant ;-)

Upvotes: 1

Related Questions