All Forums Database
ysrinu 44 posts Joined 04/07
06 Jun 2012
Update on partition by statement

Hi,

i have data:

select JOB,NAME,CODE,START_TM from table TableT

JOB,NAME,CODE,START_TM

-------------------

JOBA,M,S,2010-06-04

JOBB,M,K,2010-06-05

JOBC,N,A,2010-06-02

JOBD,N,A,2010-06-03

JOBE,O,A,2010-06-04

 

Now for each distinct value of 'NAME' (M,N,O) i want to update the CODE only for the latest START_TM

I gen get the entire record on select by:

select JOB,NAME,CODE_START_TM

from TableT1

QUALIFY ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY START_TM desc)=1

;

JOB,NAME,CODE,START_TM

-------------------

JOBB,M,K,2010-06-05

JOBD,N,A,2010-06-03

JOBE,O,A,2010-06-04

 

Now, how can i run an update statement on partition by statement.

I know we can use subqueries and not use partition by clause.

however i want to know how can i use an update on partition by select statement

 

Update on derived table doesn't work in teradata:

update tmptable from

(select JOB,NAME,CODE_START_TM

from TableT1

QUALIFY ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY START_TM desc)=1

) tmptable

set CODE='Z'

;

 

Thanks,

-srinivas yelamanchili

 

 

venkylingutla 19 posts Joined 06/12
08 Jun 2012

HI Srinivas,

find the below query which will update the latest record for the specific job name.

UPDATE UPDATE_JOB

FROM

(

SELECT NAME,MAX(STRT_TM) AS STRT FROM

UPDATE_JOB GROUP BY NAME

) A

SET CODE='D'

WHERE UPDATE_JOB.NAME=A.NAME AND STRT_TM=A.STRT;

 

thanks,

venkat

ysrinu 44 posts Joined 04/07
12 Jun 2012

Thanks Venkat, it worked !!!

You must sign in to leave a comment.