To update a table after selecting row from another table

General
Enthusiast

To update a table after selecting row from another table

Hi All,

 

Anyone please help me in writing a unix teradata.sql query for the below logic:

 

I need to update a table after running below two select statements and store it in a variable.

 

1. store the ouput from the below query in a variable as $(update_dt) 

select calendar_date - (case day_of_week when 1 then 0
when 2 then 1
when 3 then 2
when 4 then 3
when 5 then 4
when 6 then 5
when 7 then 6
end ) as sunday_of_week
from Sys_calendar.calendar
where calendar_date = add_months(cast(CURRENT_DATE as date), - 12)

 

2. Store the ouput from the below query in a variable as $(last_dt)

 

SELECT CURRENT_DATE- INTERVAL '2' YEAR

 

After this, Need to pass the output to the below update statement:


update db.table3 c
set column1 = '$(update_dt)',
column2 = '$(update_dt)',
column3 = '$(update_dt)'
where(c.column4,c.column5,c.column6) in
(select a.column4,a.column5,a.column6 from
db.table1 a,
db.table2 b
where a.column4 = b.column4
and a.column5 = b.column5
and a.column6 = b.column6
and a.column1 < '$(last_dt)')

 

 Note: Table1 and Table3 are same tables

 

Thanks! 

6 REPLIES
Junior Contributor

Re: To update a table after selecting row from another table

$(update_dt) can be calculated without a Select:

TD_SUNDAY(ADD_MONTHS(CURRENT_DATE,-12))

For $(last_dt) don't use INTERVALs as it will fail the next Feb. 29th:

ADD_MONTHS(CURRENT_DATE, -24)

 

And then there's no need for a Unix script anymore:

update db.table3 c
set column1 = TD_SUNDAY(ADD_MONTHS(CURRENT_DATE,-12)),
    column2 = TD_SUNDAY(ADD_MONTHS(CURRENT_DATE,-12)),
    column3 = TD_SUNDAY(ADD_MONTHS(CURRENT_DATE,-12))
where(c.column4,c.column5,c.column6) in
(select a.column4,a.column5,a.column6 from
db.table1 a,
db.table2 b
where a.column4 = b.column4
and a.column5 = b.column5
and a.column6 = b.column6
and a.column1 < ADD_MONTHS(CURRENT_DATE, -24))
Enthusiast

Re: To update a table after selecting row from another table

Thanks a lot for your reply. 

 

I am getting below error while running that update statement.

 

UPDATE Failed. 3706: Syntax error: expected something between '(' and the 'ADD_MONTHS' keyword.

 

I think for TD_SUNDAY function this error occurs, as we use Teradata 14.0

TD_SUNDAY(ADD_MONTHS(CURRENT_DATE,-12))

 

Please Advice. Also It will be great to update sunday day in 'yyyymmdd' format.

 

 

Thanks!

Junior Contributor

Re: To update a table after selecting row from another table

In TD14 you can use NEXT_DAY instead of TO_SUNDAY:

NEXT_DAY(ADD_MONTHS(CURRENT_DATE,-12)-7, 'SUN')
Enthusiast

Re: To update a table after selecting row from another table

Thanks a lot for your reply! It had helped me...
Junior Contributor

Re: To update a table after selecting row from another table

What's your Teradata release? Can't be TD14, because this introduced NEXT_DAY. Does SELECT NEXT_DAY(ADD_MONTHS(CURRENT_DATE,-12)-7, 'SUN') work?

Enthusiast

Re: To update a table after selecting row from another table

Yes I can able to select it... Actually there was an extra comma in my original update statement, found and removed it..

After that It worked as expected. Thanks!!