Re: Theoretical Basis for SELECT FOR UPDATE

From: Tony Andrews <andrewst_at_onetel.com>
Date: 5 Oct 2005 02:32:00 -0700
Message-ID: <1128504720.850761.44090@g49g2000cwa.googlegroups.com>

vc wrote:
> How is the database to decide what's contradictory and what's not ?

If it can detect that you are trying to set the same column to 2 different values at once, as in this case, it can raise an error - same as a SQL DBMS can in the "update t1 set x=2, x=3;" example:

SQL> update t1 set x=2, x=3;
update t1 set x=2, x=3

                   *

ERROR at line 1:
ORA-00957: duplicate column name

The only difference is that there are many more possible contradictory statements for it to anticipate.

> raise salary and fire everybody whose salary is more than 10000:
>
> update t1 set salary=salary+1000, delete from t1 where salary >10000;
>
> What would be the effect of such statement ? Is it unpredictable or
> not ? Why ?

It is predictable: it updates all salaries by 1000, and delete all t1 rows where salary was > 10000 BEFORE the statement.

To achieve effect you intended the statement would be:

update t1 set salary=salary+1000, delete from t1 where salary >9000; Received on Wed Oct 05 2005 - 04:32:00 CDT