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?
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
>
> Thanks.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
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
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.
___
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?
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'...
;)
> Thanks.
>
> Colin.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
