Update on partition by statement

Database
Enthusiast

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

Tags (2)
2 REPLIES
Enthusiast

Re: Update on partition by statement

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

Enthusiast

Re: Update on partition by statement

Thanks Venkat, it worked !!!