Re: A confusion about UPDATE command

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sat, 01 Apr 2006 00:19:43 +0200
Message-ID: <s5ar229og07u1tfc8qafju84a6lvpobufj@4ax.com>

Comments embedded

On 31 Mar 2006 13:23:45 -0800, "dn.usenet" <dn.usenet_at_gmail.com> wrote:

>
>I have two tables, both with same 2 fields for primary key and total
>four fields.
>Table-names : t11, t22
>fields : mydate varchar2(16) not null, mytime varchar2(16) not null,
>name varchar2(16)
>primary key for both tables : (mydate, mytime)
>
>t11 has 3 entries, t22 has two entries.
>update t11 t1 set t1.name = (select t2.name from t22 t2 where t2.mydate
>= t1.mydate
> and t2.mytime = t1.mytime)
>

formatted this would be

update t11 t1
 set t1.name =

    (select t2.name

      from t22 t2 
      where t2.mydate = t1.mydate
      and t2.mytime = t1.mytime

)

so no where clause, at least not on table level. This is a common mistake many people make. It should have been

update t11 t1
 set t1.name =

    (select t2.name

      from t22 t2 
      where t2.mydate = t1.mydate
      and t2.mytime = t1.mytime

)
where exists
(select 1
 from t22 t2
 where t2.mydate = t1.mydate
 and t2.mytime = t1.mytime
)

>I expect it to update 0 or 1 or 2 rows depending on how many rows of
>t11 satisfy the WHERE clause. But this command updates all 3 rows of
>t11. Why? What is the correct way of doing this type of update?
>
>Also, if I want to add more clauses for fields of t11, should I add
>them within the bracket or outside of it or in both the places.
>
>case a) update t11 t1 set t1.name = (select t2.name from t22 t2
> where t2.mydate = t1.mydate and t2.mytime = t1.mytime and t1.name =
>'paul' )
>case b) update t11 t1 set t1.name = (select t2.name from t22 t2
> where t2.mydate = t1.mydate and t2.mytime = t1.mytime )
> where t1.name = 'paul'
>case b) update t11 t1 set t1.name = (select t2.name from t22 t2
> where t2.mydate = t1.mydate and t2.mytime = t1.mytime and t1.name =
>'paul' )
> where t1.name = 'paul'
>
>Thanks in advance.

None of them is correct. If t1.mydate, t1.mytime don't occur in t2, t1.name will be updated to NULL.
Youd need the first b, with the subquery I added to your first query added to this statement with AND
so

update t11 t1
 set t1.name =

    (select t2.name

      from t22 t2 
      where t2.mydate = t1.mydate
      and t2.mytime = t1.mytime

)
where exists
(select 1
 from t22 t2
 where t2.mydate = t1.mydate
 and t2.mytime = t1.mytime
)
and t1.name = 'paul'

As the subquery in the set clause is a correlated subquery (the where clause in the subquery is referring to the main query) and you are using unique keys, you shouldn't need t1.name='paul' in your subquery

--
Sybrand Bakker, Senior Oracle DBA

Received on Fri Mar 31 2006 - 16:19:43 CST