> From: contactcolincuthb...@hotmail.com
> To: sqlite-users@sqlite.org
> Date: Mon, 4 Apr 2011 13:12:02 +0000
> Subject: Re: [sqlite] Question:how to insert row with multiple values from
> same field of different rows of another table?
>
>
>
>
> > From: punk.k...@gmail.com
> > Date: Mon, 4 Apr 2011 08:06:31 -0500
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] Question:how to insert row with multiple values from
> > same field of different rows of another table?
> >
> >
> > On Apr 4, 2011, at 7:59 AM, Colin Cuthbert wrote:
> >
> > >
> > >
> > >
> > >> From: punk.k...@gmail.com
> > >> Date: Sun, 3 Apr 2011 07:52:42 -0500
> > >> To: sqlite-users@sqlite.org
> > >> Subject: Re: [sqlite] Question:how to insert row with multiple values
> > >> from same field of different rows of another table?
> > >>
> > >>
> > >> On Apr 3, 2011, at 7:50 AM, Luuk wrote:
> > >>
> > >>> On 03-04-2011 14:43, Colin Cuthbert wrote:
> > >>>> First time I've used this (or any!) mailing list, so sorry if I've
> > >>>> done something wrong.
> > >>>>
> > >>>> Pretty sure my question (in the subect) is phrased badly but it's the
> > >>>> best I could do!
> > >>>>
> > >>>> create table People(id integer primary key, name text);
> > >>>> insert into People (name) values ('bob');
> > >>>> insert into People (name) values ('fred');
> > >>>>
> > >>>> create table Cars(id integer primary key, name text);
> > >>>> insert into Cars (name) values ('ford');
> > >>>> insert into Cars (name) values ('volvo');
> > >>>>
> > >>>> create table CarOwners(id integer primary key, carId integer
> > >>>> references Cars(id), ownerId integer references People(id));
> > >>>> insert into CarOwners (carId, ownerId) select Cars.id, People.id from
> > >>>> Cars, People where Cars.name='ford' and People.name='bob';
> > >>>>
> > >>>> create table Couples(id integer primary key, personId1 integer
> > >>>> references People(id), personId2 integer references People(id));
> > >>>>
> > >>>> The last 'insert' statement seems to work for inserting a row into the
> > >>>> 'CarOwners' table, but I'm not sure that's the right/best way to do it.
> > >>>>
> > >>>> But how can I do a similar insert into the 'Couples' table? ie, how
> > >>>> can I insert a row (specifying 'personId1' and 'personId2' via queries
> > >>>> based on 'People.name') into the 'Couples' table?
> > >>>
> > >>> You forgot to define 'Couples'.....
> > >>>> Does it start something like this?
> > >>>>
> > >>>> insert into Couples (personId1, personId2) select id, id from People
> > >>>> where...
> > >>>
> > >>> select id, id from People will return the same id (from the same record)
> > >>> twice....
> > >>>
> > >>> somehting like:
> > >>> select a.id, b.id from People a join People b on a.id<>b.id
> > >>> will give other results, but what youactually want to be returned
> > >>> depends on the definition of a 'Couple'...
> > >>>
> > >>
> > >> You also want to do all of the above in a TRANSACTION, preferably with a
> > >> TRIGGER, to ensure the correct relationships are preserved.
> > >
> > > Ok I looked into transactions (I'm new to sql!). Isn't a transaction
> > > automatically created with the insert statement? Or are you saying I
> > > need to explicitly begin/end one as part of the solution to my problem?
> >
> >
> > Yes, you need explicit BEGIN/END to perform a transaction.
> >
> > >
> > > And regarding a trigger to ensure the correct relationships are
> > > preserved... yeah you're right, but that's another issue isn't it? Or is
> > > it related to this issue in a way that I'm not seeing?
> >
> > Another, but related issue.
> >
> > Regarding your original problem, the following works
> >
> > INSERT INTO Couples (personId1, personId2) VALUES ((SELECT id FROM People
> > WHERE name = 'bob'), (SELECT id FROM People WHERE name = 'fred'));
> > sqlite> SELECT * FROM Couples;
> > id personId1 personId2
> > ---------- ---------- ----------
> > 1 1 2
> >
>
> Ha! You're brilliant! Thank you :)
>
> I think I tried that but without the parentheses around the individual select
> statements... would that have made a difference? Or maybe I didn't try that
> at all :) Either way, thanks again!
>
Discovered that this works too:
insert into Couples (personId1, personId2) select p1.id, p2.id from People p1,
People p2 where p1.name='fred' and p2.name='bob';
...and I think I prefer that.... but stil, thanks for the help!
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
