|
|
Posted by Jim Michaels on 02/24/06 04:29
"Josh McFarlane" <darsant@gmail.com> 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.
Navigation:
[Reply to this message]
|