Re: update failing for no apparent reason?

"Robert William Vesterman" <bob.work_at_vesterman.com> wrote in message news:9qq7hto3vnj1kknbsrqlhbgt179sab2ltg_at_4ax.com...
> Can anyone explain the following behaviour?
>
> SQL> update office_table set ot_state = 13 where ot_mtp_id = 369 and
> ot_state = 2;
> update office_table set ot_state = 13 where ot_mtp_id = 369 and
> ot_state = 2
> *
> ERROR at line 1:
> ORA-01403: no data found
>
>
> SQL> select count(*) from office_table where ot_mtp_id = 369 and
> ot_state = 2;
>
> COUNT(*)
> ----------
> 1
>
> SQL> update office_table set ot_office = 'TEST' where ot_mtp_id = 369
> and ot_state = 2;
>
> 1 row updated.
>
> SQL> rollback;
>
> Rollback complete.
>
> SQL> select ot_office_id from office_table where ot_mtp_id = 369 and
> ot_state = 2;
>
> OT_OFFICE_ID
> ------------
> 748
>
> SQL> update office_table set ot_state = 13 where ot_office_id = 748;
> update office_table set ot_state = 13 where ot_office_id = 748
> *
> ERROR at line 1:
> ORA-01403: no data found
>
>
> SQL> select count(*) from office_table where ot_office_id = 748;
>
> COUNT(*)
> ----------
> 1
>
>
> SQL> update office_table set ot_state = 13 where ot_office_id in (
> select ot_office_id from office_table where ot_mtp_id = 369 and
> ot_state = 2 );
> update office_table set ot_state = 13 where ot_office_id in ( select
> ot_office_id from office_table where ot_mtp_id = 369 and ot_state = 2
> )
> *
> ERROR at line 1:
> ORA-01403: no data found
>
>
> I am at a loss here. It seems like I can update the row as long as I
> don't try to update the ot_state column; if I try that, it tells me
> that there's no such row. That's regardless of whether I also refer
> to ot_state in the where clause (although I don't think that should
> matter anyway). Anybody have any idea what could be wrong?
>
> This is with Oracle 8i on an HP-UX machine.
>
> Bob Vesterman.

Question (before someone starts shouting BUG), just to verify do you have any triggers on that table with a select into, where you also don't trap the no_data_found exception?

Regards,

Sybrand Bakker, Oracle DBA Received on Tue May 29 2001 - 14:54:04 CDT