Subselect query for a multi table insert single query

Subselect query for a multi table insert single query

Post by Norman Khin » Tue, 24 Sep 2002 06:18:46



Hello,
I am using Zope with Postgre with Psycopg. Now I have 4 related tables, and
would like to write a query which will input data from one html form.

So here are my tree sql statements, is there a way to make them into one:

1) INSERT INTO business_name (business_name, business_url)
  values ('<dtml-var business_name>', '<dtml-var business_url>');

2) select last_value from business_name_business_name_seq

3) INSERT INTO business_address (street_name, town, county, postcode,
county_id, business_name_id)
  values (<dtml-sqlvar street_name type="string">, <dtml-sqlvar town
type="string">, <dtml-sqlvar county type="string">, <dtml-sqlvar postcode
type="string">, <dtml-sqlvar county_id type="int">, <dtml-var last>);

This works for zope, where the last SQL Method calls a DTML Method "last"
which basically calls the 2nd SQL statement which returns the last value
from the business name sequence table.

Obvoiusly we come to the race condition realm here which may cause a foreign
key being allocated to the wrong business_address, should two users add a
record but the first user's line slows down for a fraction so that his
second sql method returns the value from the 2nd user -- you see my
problem;^)

What was suggested was to write a subselect query, which would first insert
the business_name, then selects the last_value from
business_name_business_name_seq  and then insert this value into the
business_address table ..... an so on. I would like to do this for all 4
tables!!!

Is this at all possible if so can you provide me with links to example code
or be good enough to reply to me.

many thanks

Norm

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly

 
 
 

Subselect query for a multi table insert single query

Post by Tom La » Tue, 24 Sep 2002 07:03:39



> 1) INSERT INTO business_name (business_name, business_url)
>   values ('<dtml-var business_name>', '<dtml-var business_url>');
> 2) select last_value from business_name_business_name_seq

Instead use

        select currval('business_name_business_name_seq');

to get the assigned sequence value without a race condition.  See
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/functions...

Actually you don't need to bother with the separate select, unless
your client code needs that ID for other purposes.  You could just
write the currval() call in the second INSERT.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------


 
 
 

Subselect query for a multi table insert single query

Post by Norman Khin » Tue, 24 Sep 2002 13:08:35


 Hi Tom,
 Thanks for this, would the sql be something like

 INSERT INTO business_address (business_name_id) select
 currval('business_name_business_name_seq');

 Is this correct? If so, how do you Insert the other values that are not
 associated with the sequence i.e address, town etc....

 can i do something like

 INSERT INTO business_address (street_name, town, county, postcode,
 county_id, business_name_id) values (<dtml-sqlvar street_name
 type="string">, <dtml-sqlvar town type="string">, <dtml-sqlvar county
 type="string">, <dtml-sqlvar postcode
 type="string">, <dtml-sqlvar county_id type="int">, select
 currval('business_name_business_name_seq');

 This does not work?!As I get a parse error near "select"

 What should be the correct syntax for embedding this.

 Thanks

 Norm

 ----- Original Message -----



> Sent: Sunday, September 22, 2002 11:03 PM
> Subject: Re: [NOVICE] Subselect query for a multi table insert single
query


> > > 1) INSERT INTO business_name (business_name, business_url)
> > >   values ('<dtml-var business_name>', '<dtml-var business_url>');

> > > 2) select last_value from business_name_business_name_seq

> > Instead use

> > select currval('business_name_business_name_seq');

> > to get the assigned sequence value without a race condition.  See

http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/functions...

Quote:> ce.html

> > Actually you don't need to bother with the separate select, unless
> > your client code needs that ID for other purposes.  You could just
> > write the currval() call in the second INSERT.

> > regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

 
 
 

Subselect query for a multi table insert single query

Post by Tom La » Tue, 24 Sep 2002 13:18:53



>  What should be the correct syntax for embedding this.

This would work fine:

INSERT INTO table VALUES (foo, bar, baz, currval('seq'), ...);

The elements of an INSERT/VALUES list are expressions, not
necessarily literal constants.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------

 
 
 

Subselect query for a multi table insert single query

Post by Norman Khin » Tue, 24 Sep 2002 15:24:06


Many thanks
----- Original Message -----



Sent: Monday, September 23, 2002 5:18 AM
Subject: Re: [NOVICE] Subselect query for a multi table insert single query


> >  What should be the correct syntax for embedding this.

> This would work fine:

> INSERT INTO table VALUES (foo, bar, baz, currval('seq'), ...);

> The elements of an INSERT/VALUES list are expressions, not
> necessarily literal constants.

> regards, tom lane

---------------------------(end of broadcast)---------------------------


 
 
 

Subselect query for a multi table insert single query

Post by nor.. » Tue, 24 Sep 2002 18:56:34


Hi Tom,
I tried this on the command prompt but got the following error:

admin=> INSERT INTO business_address (street_name, town, county, postcode, county_id, business_name_id) values ('xcasc', 'ascasc', 'ascasc', 'acasc', 2, currval('business_name_business_name_seq'));
ERROR:  business_name_business_name_seq.currval is not yet defined in this session

Any ideas as to what is going on.

Cheers

Norm

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

 
 
 

1. MySQL won't let me update on multi-table query

Hi All.

I've got a MySQL db with a mix of MyISAM and BDB tables. The front-end
is Access 97. I've got a form in Access based on a query that joins
two BDB tables. But Access is telling me that the query is not
updateable.

I know this probably isn't enough information. If you can help anyway,
I'd appreciate it.

Thanks.

Danny

2. NMv2.0 and Exchange Server v5.0

3. Queries against multi-million record tables.

4. Does anyone understand "solid" brushes?

5. Querying against a DataWindow - or - How to do this query?

6. ORACLE DBA/ CONTRACT/ NC

7. Nested queries/sub-queries...

8. Query query

9. VMware query / Acme query

10. Concatenate results of a single column query