Possible to copy data and update a field at the same time?

I've got a structure where I have a database that I want to merge into
another (same format), however, I need to update some fields.

Is it possible to do an INSERT and an UPDATE in the same SQL statement
without modifying the original data with the update statement? (Making
a modification to the recordset in memory that you are inserting)

I'd like to do the following:

Insert data into first new table
Retreive new primary key ID for each record

Pull child records out of second table
Update foreign key to new key from above
Insert records into new table

Thanks in advance!
Darsant

Re: Possible to copy data and update a field at the same time?

Post removed (X-No-Archive: yes)

Re: Possible to copy data and update a field at the same time?

"Josh McFarlane" wrote in message
news:1137011309.156279.200480@g47g2000cwa.googlegroups.com.. .
> I've got a structure where I have a database that I want to merge into
> another (same format), however, I need to update some fields.
>
> Is it possible to do an INSERT and an UPDATE in the same SQL statement
> without modifying the original data with the update statement? (Making
> a modification to the recordset in memory that you are inserting)
>
> I'd like to do the following:
>
> Insert data into first new table
> Retreive new primary key ID for each record
>
> Pull child records out of second table
> Update foreign key to new key from above
> Insert records into new table
>
> Thanks in advance!
> Darsant
>

START TRANSACTION;
Insert data into first new table;
Retreive new primary key ID for each record;
Pull child records out of second table;
Update foreign key to new key from above;
Insert records into new table;
COMMIT;

this makes the whole thing atomic.

you can't put an INSERT and an UPDATE in the same statement. but you can do
the above. and if the transaction doesn't work out for you, you can do a
ROLLBACK to undo it.
I don't know if this is necessarily what you were looking for, but I hope it
helps.