Good point...neither one is probably desirable...
When you get to this point in your code you should already have P_Id being passed around and not have to retrieve it. Retrieving is dangerous as Igor has just described unless you have last name with a unique constraint (which would seem have far too much potential for duplicates). You would want to catch the error where LastName does not exist so it probably should be in another select statement by itself. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate ________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Igor Tandetnik [itandet...@mvps.org] Sent: Monday, August 15, 2011 6:58 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Insert rows into a foreign key table Black, Michael (IS) <michael.bla...@ngc.com> wrote: > insert into orders (OrderNo,P_Id) select 12345,P_Id from persons where > LastName='Hansen'; vs > INSERT INTO ORDERS (OrderNo, P_Id) > values ( > 12345, > select P_Id from persons where LastName = 'Hansen') To the OP: note that there's a subtle difference, which may or may not matter for your use case. If there are several records with LastName = 'Hansen', then the first statement will insert a row into orders for each one of them; if there are none, it won't insert anything. The second statement (once corrected) always inserts one row, picking one of the Hansens in an unpredictable manner, or inserts NULL if there are none. -- Igor Tandetnik _______________________________________________ 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
