Update for multiple tables

Hello,

I am trying to write a sql script (I'm pretty much a novice at SQL) that will update two tables without violating integrity constraints. I realize that there is a limitation in Oracle with doing this.

Table1
CKPT_ID PK Table2
CKPT_ID FK I want to update the ckpt_id value in Table1 to 5036 (currently the value is 5044) as well as the 2 child records in Table2 that currently contain the same value (5044) to 5036. Because there is a PK-FK constraint on the tables, I am hoping that a correctly written SQL statement will update both correctly and not violate the constraint.

I have been trying to come up with a nested subquery, I am still trying to determine whether using an EXIST clause or an IN clause would help as well. This what I have so far:

UPDATE Table1 c
SET ckpt_id = 5036
(SELECT ckpt_id
FROM Table2
WHERE ckpt_id =c.ckpt_id)
WHERE ckpt_id = 5044;

Any help would be appreciated. Thanks in advance. Received on Tue Jun 26 2007 - 13:50:43 CDT