All Forums Database
David Korb 8 posts Joined 07/09
17 Jul 2009
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!

Fred 1096 posts Joined 08/04
17 Jul 2009

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;

David Korb 8 posts Joined 07/09
17 Jul 2009

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

You must sign in to leave a comment.