Teradata Update Syntax

Database
Enthusiast

Teradata Update Syntax

Hi Everyone,

I need to update table records in teradata using SQL codes.  I need to conversation sql codes to teradata syntax. Has someone got any iddia how to convert following sql codes to teradata sytax. 

UPDATE Table_A K

        FROM

          (

             SELECT b.Column_1 ,b.Column_2,b.Column_3 ,b.Column_4 

           from Table_A a  inner join  

              (

                select 20120601 as Column_1,5 as Column_2 ,

                          COUNT(*) as Column_3,

                         (SUM(Column_6) +sum(Column_7)) as Column_4  

               from Table_B a inner join   Table_C b 

                                             on a.column_5=b.column_5    

          where Column_2=5  and Column_1= 20120601 b and a.Column_1=b.Column_1 

                      and  a.Column_1= 20120601 and a.Column_2=5 ) M

SET K.Column_3=M.Column_3 and K.Column_4=M.Column_4  

Thanks in advance

11 REPLIES
Senior Supporter

Re: Teradata Update Syntax

Hi,

it would be easier if you would describe what the update should do.

So I could only give a guess from the code...

UPDATE K 
FROM Table_A
(
select a.Column_1,a.Column_2 ,
COUNT(*) as Column_3,
(SUM(Column_6) +sum(Column_7)) as Column_4
from Table_B a inner join Table_C b
on a.column_5=b.column_5
where a.Column_1=b.Column_1
and a.Column_2=b.Column_2
and a.Column_1= 20120601 and a.Column_2=5
) M
SET Column_3=M.Column_3
,Column_4=M.Column_4
where --> table_a and derived table M join condition;

So you specify your dervied table in the from clause.

and define in the where clause the join condition between the table_a and the derived table M.

Enthusiast

Re: Teradata Update Syntax

Hi,

Thanks for your advice. I think the problem is that joining data (select statements) and  update clause.

I will try to run script as you write. If i get error, i will share more details of error. Next time, i will give more details; table names, column names,... etc. So, it will be better way to help me.

Enthusiast

Re: Teradata Update Syntax


Hi, 

I try to run sql script as you write, but it does not run. Sql codes are here. I think there is no problem to give a error. I could not find out errors;

update cust_zone_sales k

from

(select main_zone_id, zone_no, sales_count, sales_sum

from 

(

select 1420 as main_zone_id, 17 as zone_no, count(*) as  sales_count, 

(sum(usd_amount) + sum(euro_amount) ) as sales_sum 

from new_cust_table a

inner join new_dealer_table b 

on a.prodid = b.prodid

where proddescid = 5

) b

on a.tran_area_id = b.tran_area_id

and a.main_zone_id = b.main_zone_id

where a.main_zone_id = 1265

and a.zone_no = 48

and zone_status_flag = 'F'

) M

set k.sales_count = m.sales_count

and k.sales_sum = m.sales_sum

where 

k.main_zone_id=m.main_zone_id and 

k.zone_no= m.zone_no 

Senior Supporter

Re: Teradata Update Syntax

Check the Update syntax diagram in the manuals

What I can see is the the set needs a , instead of an and 

something like 

update k

from
cust_zone_sales k,

(select main_zone_id, zone_no, sales_count, sales_sum

from

(

select 1420 as main_zone_id, 17 as zone_no, count(*) as sales_count,

(sum(usd_amount) + sum(euro_amount) ) as sales_sum

from new_cust_table a

inner join new_dealer_table b

on a.prodid = b.prodid

where proddescid = 5

) b

on a.tran_area_id = b.tran_area_id

and a.main_zone_id = b.main_zone_id

where a.main_zone_id = 1265

and a.zone_no = 48

and zone_status_flag = 'F'

) M

set k.sales_count = m.sales_count

,k.sales_sum = m.sales_sum

where

k.main_zone_id=m.main_zone_id and

k.zone_no= m.zone_no

Enthusiast

Re: Teradata Update Syntax

I tried ,but it gave me a new error [3993 - illegal usage of alias name] now.  

Senior Supporter

Re: Teradata Update Syntax

Didn't rec it in the last post.

You need to fix your derived table...

 

on a.tran_area_id = b.tran_area_id

 

and a.main_zone_id = b.main_zone_id

 

where a.main_zone_id = 1265

 

and a.zone_no = 48

 

and zone_status_flag = 'F'

 

 

where is a comming from?

 


Enthusiast

Re: Teradata Update Syntax

zone_status_flag ?

zone_status_flag comes from new_cust_table. 

Enthusiast

Re: Teradata Update Syntax

And This column's name is unique

Senior Supporter

Re: Teradata Update Syntax

You need to write the derived table M in a way that you can execute it on itself (copy / paste into SQLA) and gives a result set which you can join to the target table.