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
