All Forums Database
ramirhan 6 posts Joined 06/12
13 Aug 2012
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

 

 

ulrich 816 posts Joined 09/09
13 Aug 2012

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.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ramirhan 6 posts Joined 06/12
13 Aug 2012

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.

 

 

ramirhan 6 posts Joined 06/12
13 Aug 2012

 

 

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 

ulrich 816 posts Joined 09/09
13 Aug 2012

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 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ramirhan 6 posts Joined 06/12
13 Aug 2012

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

ulrich 816 posts Joined 09/09
13 Aug 2012

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?

 

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ramirhan 6 posts Joined 06/12
14 Aug 2012

zone_status_flag ?

zone_status_flag comes from new_cust_table. 

ramirhan 6 posts Joined 06/12
14 Aug 2012

And This column's name is unique

ulrich 816 posts Joined 09/09
14 Aug 2012

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.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

bertin88 4 posts Joined 05/11
04 Sep 2012

 [3993 - illegal usage of alias name] occured due to alias name for target table in SET. Rewrite below part,

set k.sales_count = m.sales_count
,k.sales_sum = m.sales_sum

to

set sales_count = m.sales_count
,sales_sum = m.sales_sum

 

 

venkylingutla 19 posts Joined 06/12
10 Oct 2012

 
UPDATE cust_zone_sales k
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
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' AND proddescid = 5
) 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
Thanks,
Venkat

You must sign in to leave a comment.