Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Update for multiple tables

Re: Update for multiple tables

From: <admtechnologies_at_gmail.com>
Date: Tue, 26 Jun 2007 14:08:09 -0700
Message-ID: <1182892089.049880.301660@d30g2000prg.googlegroups.com>


Thanks,

I have modified it a bit, like so:

insert into table1 (CKPT_ID, COORD_ID, DISTANCE_NUM, CRE_UID, CRE_DTS, UPDT_LOCK_NUM, COORD_NAM_TXT)
VALUES ('5036', '5552', '168', 'ADM', '2007-06-26', '0', 'City') where ckpt_id = 5044;

update table2
set ckpt_id = 5036
where ckpt_id = 5044;

delete from table1
where ckpt_id = 5044;

commit;

When I run it as a script in TOAD, I get the following error: ORA-00933: SQL command not properly ended. Any ideas?

On Jun 26, 12:27 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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 - 16:08:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US