Update with two joins

Database
Enthusiast

Update with two joins

Hi,

I am having trouble with writing an update statement. Below is the SQL code to understand basic update I need:

UPDATE ndeshpande.VW_ClickPref_URLExtract Extr
SET extr.URL_Attraction_ID = atr.attraction_id
FROM
pfocusvw.event AS ev ON extr.URL_Extract = ev.Event_ID_Hex
INNER JOIN
pfocusvw.EVENT_ATTRACTION AS atr ON ev.Event_ID = atr.Event_ID
WHERE CHARACTER_LENGTH(extr.URL_Extract)>10
AND atr.Event_Attraction_Rank = 1

I am not able to write the update statement for this in teradata.  I tried using subquery and derived table but I cannot get them to work. Can you please help me here?

Thanks,

Nik

Tags (2)
2 REPLIES
Enthusiast

Re: Update with two joins

I was able to construct the statement like this:

UPDATE ndeshpande.VW_ClickPref_URLExtract
FROM
(SELECT atr.*, ev.event_id_hex FROM ndeshpande.VW_ClickPref_URLExtract EA
INNER JOIN pfocusvw.event AS ev ON EA.URL_Extract = ev.Event_ID_Hex
INNER JOIN pfocusvw.EVENT_ATTRACTION AS atr ON ev.Event_ID = atr.Event_ID
WHERE CHARACTER_LENGTH(ea.URL_Extract)>10
) AS b
SET URL_Attraction_ID = b.attraction_id
WHERE b.event_id_hex = ndeshpande.VW_ClickPref_URLExtract.URL_Extract;

but this takes too much space even while calculating the result set and the query failes due to it. Is there a more simplified version of this?

-Nik

Teradata Employee

Re: Update with two joins

UPDATE Extr FROM

FROM ndeshpande.VW_ClickPref_URLExtract AS Extr,

     pfocusvw.event AS ev,

     pfocusvw.EVENT_ATTRACTION AS atr

SET URL_Attraction_ID = atr.attraction_id       

WHERE extr.URL_Extract = ev.Event_ID_Hex

AND ev.Event_ID = atr.Event_ID

AND CHARACTER_LENGTH(extr.URL_Extract)>10         

AND atr.Event_Attraction_Rank = 1