All Forums Database
Nikhil_Teradata 24 posts Joined 10/13
02 Jan 2016
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

--Nik

"Learn, Learn, Learn and your life would never be boring"

Nikhil_Teradata 24 posts Joined 10/13
03 Jan 2016

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

--Nik
"Learn, Learn, Learn and your life would never be boring"

Fred 1096 posts Joined 08/04
04 Jan 2016

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

You must sign in to leave a comment.