| Re: Simple Update [message #374911] |
Wed, 11 July 2001 08:21  |
Atul Kotkar
Messages: 2
Registered: July 2001
|
Junior Member |
|
|
Hi, Mike.
Your T2 table may return more than one Row with the condition u've specified. Bcoz i tried same it gives me the Error of 'single-row subquery returns more than one row'.
So check this if this occurs.
Regards,
Atul
|
|
|
|
| Re: Simple Update [message #374914 is a reply to message #374911] |
Wed, 11 July 2001 12:19  |
Peter A
Messages: 2
Registered: July 2001
|
Junior Member |
|
|
I agree with Mike -- this is trivial in SQLServer or even Access, so there must be a way to do this with simple SQL. I haven't found it, so I resorted to this type of PL/SQL loop:
declare
cursor T2_cursor is
select Field1, Field2
from Table2;
begin
for T2_record in T2_cursor loop
update Table1 set
Field1 = T2_record.Field1
where Field2 = T2_record.Field2;
end loop;
end;
/
If anyone knows of an easier way to update one table based on multiple values in another, I'd love to hear it!
-Peter
|
|
|
|
| Re: Simple Update [message #374924 is a reply to message #374911] |
Wed, 11 July 2001 18:35 |
Peter A
Messages: 2
Registered: July 2001
|
Junior Member |
|
|
I found an arguably simpler way of doing this, with 2 nested subqueries. Let me know if this works for you:
UPDATE Table1 T1
SET Field1 =
(SELECT Field1
FROM Table2 T2
WHERE T1.Field2=T2.Field2)
WHERE Field2 =
(SELECT Field2
FROM Table2 T2
WHERE T1.Field2=T2.Field2);
I'm still not sure why this works -- it seems strange to not specify the joined tables in the FROM clause of each subquery.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
