To update a table after selecting row from another table

[Arunsak4]

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! 

[dnoeth]

$(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))

[Arunsak4]

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!

[dnoeth]

In TD14 you can use NEXT_DAY instead of TO_SUNDAY:

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

[Arunsak4]

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

[dnoeth]

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?

[Arunsak4]

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