Insert keys from 1 table into 2 others.

[M F]

I am creating a table which will be used by 2 other tables.

Explanation:

NEWTABLE has 2 columns: ID (autoincrement) and VALUE
TABLEA gets new column: NEWTABLE_ID, old columns: ID, AVALUE
TABLEB gets new column: NEWTABLE_ID, other columns: ID, BVALUE

I add new rows to NEWTABLE for TABLEA and TABLEB with 2 statements: (ID gets auto value)

INSERT INTO NEWTABLE (VALUE)
SELECT 'default' from TABLEA

INSERT INTO NEWTABLE (VALUE)
SELECT 'default' from TABLEB

Now NEWTABLE has X number rows where X is number of rows in TABLEA + TABLEB.

Say n is the number of rows in TABLEA

I want to update NEWTABLE_ID in TABLEA with the first n values of NEWTABLE and then update TABLEB with x-n values. Please tell me how to do this, it doesn't have to be in just one statement.

I could also have updated NEWTABLE_ID in TABLEA and TABLEB before inserting rows to the new table but not sure how to do that either. If you have a solution for that instead, that would be great as well.

Thanks.



Edited 1 time(s). Last edit at 03/14/2012 05:45PM by M F.