Re: help with my sql

On Thu, 22 Aug 2002 00:03:20 GMT, "Joe Sath" <dbadba62_at_hotmail.com> wrote:

>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
>
>
>
>

Assuming that A.date_key and B.date_key are unique, this should do the work :

update A
  set A.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 date_key =   ( select a.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'));		

• Kenneth Koenraadt