Re: Update for multiple tables

On Jun 26, 1:50 pm, admtechnolog..._at_gmail.com wrote:
> 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.

insert into table1
(<column list here>)
select 5036, <rest of columns here> from table1 where ckpt_id = 5044;

update table2
set ckpt_id = 5036
where ckpt_id = 5044;

delete from table1
where ckpt_id = 5044;

commit;

David Fitzjarrell Received on Tue Jun 26 2007 - 14:27:12 CDT