update and inner join issue

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

update and inner join issue

Hi,

 

i have this code:

 

UPDATE a
SET 
 EventNum = b.EventNum,
 EventType = b.EventType,
 FromDate = b.FromDate,
 ToDate = b.ToDate,
 Last_EnterDate = GetDate(),
 TimesInOffer = TimesInOffer + 1    
from  [campaignoperatorsDB].dbo.B2C_Casino_0000_Fact_CRM_Machine_PlayersHistory a
inner join [campaignoperatorsDB].dbo.B2C_Casino_0000_Fact_CRM_Machine_PlayersInProtocols B                                                         
   on (a.CID=B.CID and a.OfferNum = B.OfferNum )

 

 

when i try convert to teradata it warn me that update order befor inner join between 2 tables not exsist in TERADATA some one know how to  present it anyway.

 

Tx!!!

4 REPLIES
Teradata Employee

Re: update and inner join issue

It would be helpful to see the translated SQL that was attempted on Teradata and the actual error message.

Junior Contributor

Re: update and inner join issue

Teradata simply doesn't allow explicit join syntax (this also means no Outer Join) for Updates, you must switch to old style comma-delimited syntax:

 

from  [campaignoperatorsDB].dbo.B2C_Casino_0000_Fact_CRM_Machine_PlayersHistory a,
      [campaignoperatorsDB].dbo.B2C_Casino_0000_Fact_CRM_Machine_PlayersInProtocols B                                                          
where (a.CID=B.CID and a.OfferNum = B.OfferNum )
Enthusiast

Re: update and inner join issue

her you can see my code after conversion and the warning error:

 

(X) UPDATE a -----------> TERADATA PUT HER ERROR WARNING

from campaignoperatorsDB.B2C_Casino_0000_Fact_CRM_Machine_PlayersHistory a,

campaignoperatorsDB.B2C_Casino_0000_Fact_CRM_Machine_PlayersInProtocols B

SET

EventNum = b.EventNum,

EventType = b.EventType,

FromDate = b.FromDate,

ToDate = b.ToDate,

Last_EnterDate = CURRENT_TIMESTAMP,

TimesInOffer = TimesInOffer+1

where((a.CID = B.CID and a.OfferNum = B.OfferNum)) AND

CAST(CAST((CURRENT_TIMESTAMP(FORMAT 'YYYYMMDD')) AS VARCHAR(30)) AS INTEGER)

not between a.FromDate and

CAST((CURRENT_DATE - INTERVAL '1' day )

CAST(CAST(SUBSTRING(a.ToDate FROM 1 FOR 8) AS TIMESTAMP(3)) AS TIMESTAMP(3)))

(FORMAT 'YYYYMMDD')) AS VARCHAR(30)) AS INTEGER);

 

 

Tx!!!

 

Enthusiast

Re: update and inner join issue

i Guess my problem was the date line , couse when i fix the code as 'dnoeth' suggested it's work.