Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm working on the exercises for where we have to write a trigger that makes new students named 'Friendly' automatically like everyone else in their grade. That is, after the trigger runs, we should have ('Friendly', A) in the Likes table for every other Highschooler A in the same grade as 'Friendly'.

This is being done in sqlite on the following dataset. Now my trigger works, but it only adds one person that 'Friendly' likes rather than all of them (those with the same grade).

Here's my trigger:

CREATE Trigger Q1
After Insert on Highschooler
For Each Row
When New.Name = 'Friendly'
    INSERT Into Likes Values (New.ID, (
    SELECT Highschooler.ID FROM Highschooler
    WHERE Highschooler.Grade = New.Grade));

I suspect there's something wrong with my INSERT query, because in isolation the SELECT returns all the IDs. So how do I make sure it inserts all the IDs with matching grades?

share|improve this question
whats very interesting here is that SQLlite doesn't throw a SQL error IMO – Jack Douglas Nov 17 '11 at 16:51
up vote 10 down vote accepted

On most platforms your insert would throw an error if your subselect returns more than one row, rather than just insert the 'first' returned row (and 'first' is undefined without an order by clause).

What you are probably after is an insert like this:

INSERT Into Likes 
FROM Highschooler 
WHERE Grade = New.Grade and ID<>New.ID;
share|improve this answer
Yep! This solves my problem, it inserts all combinations rather than just the first. Thanks! – Ivo Flipse Nov 17 '11 at 21:38

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.