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.

Comments embedded



On 31 Mar 2006 13:23:45 -0800, "dn.usenet" <dn.usenet@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