A confusion about UPDATE command

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dn.usenet - 31 Mar 2006 22:23 GMT

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)

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.

Reply to this Message

Sybrand Bakker - 31 Mar 2006 23:19 GMT

Comments embedded

>I have two tables, both with same 2 fields for primary key and total
>four fields.
[quoted text clipped - 7 lines]
>= 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
[quoted text clipped - 15 lines]
>
>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

Reply to this Message