Re: Insert Data Into Tables Linked by Foreign Key

On 4 Jan 2010, at 9:53, Yan Cheng Cheok wrote:

> For example, "John" place "1.34" priced order.
>
> (1) Get Customer_ID from Customer table, where name is "John"
> (2) If there are no Customer_ID returned (There is no John), insert "John"
> (3) Get Customer_ID from Customer table, where name is "John"
> (4) Insert "Customer_ID" and "1.34" into Order table.
>
> There are 4 SQL communication with database involved for this simple operation!!!
>
> Is there any better way, which can be achievable using 1 SQL statement?

You don't need the 3rd statement if you use INSERT .. RETURNING at step 2.

The one way you could achieve this by calling only one statement that I can think of is to wrap this in a stored procedure. Plain SQL doesn't provide any means to do what you want.

Alban Hertroys

--
Screwing up is the best way to attach something to the ceiling.

!DSPAM:737,4b41d7ac9957514533904!