Will a query of query update two tables that are joined?

Hi all,

I'm using an update query that adds a record to the employeeInfo table. I've taken the advice of certain CF masters in this forum and I've normalized my database (at least the employee section) and broken my one data table with comma lists into (hold on to something) 4 tables. One table actually joins the other three.

What's the best use of SQL to not only update the employeeInfo table but the employeeGymConnect table which is the intermediate table for joining all the other tables.

The position, gymname,programsAssocWith all have foriegn keys in the employeeGymConnect table.

I thought I might use a query inside a query to do both updates at the same time but I still can't be sure that the new record in the employeeInfo table will be the one updated in the employeeGymConnect table.

I hope I explained this clear enough. Help?

Nathan

I don't even understand how you would do this. You can run a SELECT sub-query on an UPDATE or INSERT query, but you can't run an UPDATE sub-query on one. The logic just doesn't work.
The syntax of an UPDATE or INSERT query provides for only one table name. Feel free to play around with it, but I've never seen a way to do an UPDATE or INSERT through multiple tables without running multiple queries.

Just write two queries, one for each table, and wrap them in a <cftransaction> tag so that they execute or fail as a single unit.

I guess I'm not wrapping my head around the logical sequence of events for updating two table that are joined.

I need to be sure the correct info is updated in both tables for one record. Since one table creates a AutoNumber for each new employee record and then the empGymConnect table needs to be updated but ONLY after this new record in added in the emp table.

What would the SQL look like?

Nathan

Insert the data into the base table, select the data back out if necessary to get the primary key, then do one ore more additional inserts into the related tables. Use cftransaction around the whole set of queries. Make sense?