Teradata SQL syntax for UPDATE, submitted through BTEQ

[laurieb]

I am translating an Oracle SQL query to Teradata. The task is to update 2 columns in table A, one with a constant, the other with the max value for that column in table B, where the ID number matches the ID number in table A.

This is the Teradata code:
update tableA
from tableB
set
rsn_cd = max(tableB.rsn_cd)
where tableA.ID_num = tableB.ID_num,
src_cd = 'C

where tableA.ID_num in
(select
ID_num
from tableB
)
and src_cd = 'M'
and tableA.ID_num < 9000000000000
;

This produces a syntax error &quot;3706 Syntax Error. Expected something between the word ID_num and ','&quot; - referring to the where clause in the SET statement.
A further complication is that before I added the where clause, running the query produced the message '3872 Illegal use of an aggregate operation in an UPDATE statement' and if I read the text in the error manual correctly is a dead end. So even if the syntax problem is solved, this approach still may not work.

If anyone sees anything I am doing wrong or has a different approach, please let me know. Thanks.

 

[tdgulbra]

Aggregates in &quot;update&quot; statements can be a pain. I usually write a quick view to do the aggregation and then change the update to get the values from the view instead of the table. Here is my simplification of your problem:

CREATE SET TABLE TDGULBRA.tableA ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL
(
a INTEGER,
b INTEGER,
c INTEGER)
UNIQUE PRIMARY INDEX ( a );

CREATE SET TABLE TDGULBRA.tableB ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL
(
d INTEGER,
e INTEGER,
f INTEGER)
UNIQUE PRIMARY INDEX ( d );

cv ViewB as (sel d as d,
max(f) as maxf
from TableB
group by 1);

update tableA
from viewB
set c = viewb.maxf,
b = 26
where a = viewB.d ;

One other point, there is only one where clause allowed per query, so all of your conditions must be down at the bottom in the same clause.

I hope this helps....

 

[laurieb]

That worked perfectly. Thanks.
One interesting point that I learned in testing it - Teradata insists that a DDL statement must be the last statement in it's transaction, so I had to create the view, which remains as a permanent view in the DB and then run the update as a separate submission. Very strange.
Thanks again for your help.

 

[dunko]

I don't know what release of Teradata you are on, but you should be able to create the view as a volitile temporarary table within your SQL without the need to run it seperately as described above.

This is the method that Microstrategy for example uses to run complex multipart SQL against Teradata. This is available in V2R3 and above.