Joe,
As your date key is unique across year, month, day, hour and minute you
could use aggregate functions like min or max on the subquery, which
returns a single datekey instead of returning multiple rows.
update..
set... = (select min(datekey)
.....
where exists (select datekey...);
hth,
-Stan
Joe Sath wrote:
> Sorry, it did not work.
>
> ERROR at line 2:
> ORA-01427: single-row subquery returns more than one row
>
> Any better idea?
>
> Thanks
>
> "Thomas Kyte" <tkyte_at_oracle.com> wrote in message
> news:ak1di00ab0_at_drn.newsguy.com...
>
>>In article <cTV89.7$6U3.4_at_nwrddc01.gnilink.net>, "Joe says...
>>
>>>I have two tables.
>>>table A has a date column, and a date_key,
>>>table B has a year(varchar2) , month(varchar2) , day(varchar2),
>>>hour(varchar2),minute(varchar2) column, and a date_key
>>>
>>>I need to do something like this
>>>
>>>update table A
>>>set A.date_key = B.date_key
>>>where
>>>B.year = to_char(A.date,'yyyy')
>>>and B.month = to_char(A.date,'mm')
>>>and B.day = to_char(A.date,'dd')
>>>and B.hour = to_char(A.date,'hh24')
>>>and B.minute = to_char(A.date,'mi')
>>>
>>>How could I do that?
>>>
>>>Thanks for your help
>>>
>>>
>>>
>>update A
>> set date_key = (select date_key
>> from b
>> where B.year = to_char(A.date,'yyyy')
>> and B.month = to_char(A.date,'mm')
>> and B.day = to_char(A.date,'dd')
>> and B.hour = to_char(A.date,'hh24')
>> and B.minute = to_char(A.date,'mi'))
>>where exists (select date_key
>> from b
>> where B.year = to_char(A.date,'yyyy')
>> and B.month = to_char(A.date,'mm')
>> and B.day = to_char(A.date,'dd')
>> and B.hour = to_char(A.date,'hh24')
>> and B.minute = to_char(A.date,'mi'))
>>
>>
>>
>>
>>>
>>--
>>Thomas Kyte (tkyte@oracle.com) http://asktom.oracle.com/
>>Expert one on one Oracle, programming techniques and solutions for Oracle.
>>http://www.amazon.com/exec/obidos/ASIN/1861004826/
>>Opinions are mine and do not necessarily reflect those of Oracle Corp
>>
>>
>
>
Received on Wed Aug 21 2002 - 23:24:56 CDT
