Re: Update for multiple tables

<admtechnologies_at_gmail.com> wrote in message news:1182892089.049880.301660_at_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
>
>

insert into table1 (CKPT_ID, ....)
VALUES ('5036', .....)
where ckpt_id = 5044;

You are using quotes around '5036' where ckpt_id appears to be a number based on your where clause. I suspect some of your other values have the same problem.

-- 
Terry Dykstra