Update query problems

[tyson crouch]

G'day,

I'm having a problem which constructing an appropriate query for updating two tables simultaneously.

My tables are as such:

Tabel A
attID


Table B
pID
pPrice


Table C
attID
pID


**Note:
table A PK is an auto-increment field
table A has a 1:M relationship with table C
table B has a 1:M relationship with table C

The problem is as follows:

1. Table A and B are to be updated simultaneously.
2. While updating table B, the primary key from the update of table A is to be used as a foreign key.


So my question is; How do i run a query to insert a price for table A while using the pID generated to insert into table B. Below is an example of the queries which i think may be useful in further understanding what it is that i am trying to achieve.

INSERT INTO table B values (NULL, 'price');
INSERT INTO table C values ('pID' , 'AttID'); <-- the pID from this insert is to be obtained from the query above

Your help is MUCH appreciated.

Regards,
TC



Edited 2 time(s). Last edit at 04/11/2010 02:00AM by tyson crouch.