On 15 Aug 2011, at 12:58pm, Igor Tandetnik wrote: > Black, Michael (IS) <[email protected]> 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.
which is why proper code should never do it in one instruction. You do the SELECT first, then use your programming language to do the right thing for each of the three cases where 0, 1 or more records are returned. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
