Database trigger select multiple rows and insert into table

Question

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)

Mikajlo8 · Accepted Answer · 2015-02-11 00:13:49Z

1

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 ;-)

answered Feb 11, 2015 at 0:13

Mikajlo8

813 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Database trigger select multiple rows and insert into table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related