Re: [sqlite] Question:how to insert row with multiple values from same field of different rows of another table?
> From: contactcolincuthb...@hotmail.com
> To: sqlite-users@sqlite.org
> Date: Mon, 4 Apr 2011 13:12:02 +
> 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
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!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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: 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?
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?
Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question:how to insert row with multiple values from same field of different rows of another table?
> Date: Sun, 3 Apr 2011 14:50:30 +0200
> From: luu...@gmail.com
> 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 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'...
>
> ;)
Sorry for not being clear.
The row I want to insert into the Couples table would contain, for example:
personId1 = bob's id (ie the 'id' member of the 'People'-table-row whose 'name'
member is 'bob')
personId2 = fred's id (ie the 'id' member of the 'People'-table-row whose
'name' member is 'fred')
So what I want to do is search the People table to find the id's of bob and
fred and add a row to the Couples table containing those id's all in one
insert statement.
Is that clearer? Or possible?
Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question:how to insert row with multiple values from same field of different rows of another table?
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?
Does it start something like this?
insert into Couples (personId1, personId2) select id, id from People where...
Thanks.
Colin.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
