Joined table update

Database
Enthusiast

Joined table update

Hi, I've just arrived to the Teradata world from SQL Server and learning the new syntax. How do you update a table using a join?

The first approach I used was what I have been doing: create aliases, then refer to them, but I get 3993: Illegal use of alias name.


bt;
update login.table_aggregate as AGG
from login.table_temp as TMP
set AGG.period = TMP.period
,AGG.events = TMP.events
where AGGcustomer_id = TMP.customer_id
and AGG.period < TMP.period;
et;


Then I tried reading the documentation and googling to try aliasing only the FROM table, but got 3810: Column/parameter _table_reference_ does not exist.


bt;
update login.table_aggregate
from login.table_temp as TMP
set login.table_aggregate.period = TMP.period
,login.table_aggregate.events = TMP.events
where login.table_aggregate.customer_id = TMP.customer_id
and login.table_aggregate.period < TMP.period;
et;


Help! and TIA!
Tags (2)
2 REPLIES
Teradata Employee

Re: Joined table update

The main thing is you're not allowed to qualify the column name on the left-hand side of the SET clause at all. Omitting the alias on the target table, syntax would be:

update login.table_aggregate
from login.table_temp as TMP
set period = TMP.period
,events = TMP.events
where login.table_aggregate.customer_id = TMP.customer_id
and login.table_aggregate.period < TMP.period;

Or using the alias:

update AGG
from login.table_aggregate as AGG
, login.table_temp as TMP
set period = TMP.period
,events = TMP.events
where AGG.customer_id = TMP.customer_id
and AGG.period < TMP.period;
Enthusiast

Re: Joined table update

Thanks for the help Fred. Wow, this is more painful than I expected!