Update statement with left outer join in from clause.

Database
Enthusiast

Update statement with left outer join in from clause.

Hi,

Please guide on how to write left outer join inside update statement in Teradata. Below is one of sql that we need to run on Teradata.  

UPDATE Staging.CDD_Today

SET ABC_Risk = b.riskfactor

FROM Staging.CDD_TodayCDD_CountryRisk a

LEFT JOIN dbo.CDD_ABC_Uplift b ON b.REVIEW_ID = a.REVIEW_ID

AND b.COUNTRY_NAME = a.ABC_Country

Thanks

6 REPLIES
Senior Apprentice

Re: Update statement with left outer join in from clause.

Hi Harpreet,

is this an actually update statement? (SQL Server?)

There's no condition to join to CDD_Today.

You might rewrite it:

  • use a Correlated Subquery 

UPDATE Staging.CDD_Today

SET ABC_Risk = 

 ( SELECT b.riskfactor

   FROM Staging.CDD_TodayCDD_CountryRisk a

   LEFT JOIN dbo.CDD_ABC_Uplift b ON b.REVIEW_ID = a.REVIEW_ID

   AND b.COUNTRY_NAME = a.ABC_Country

)

  • move the join into a Derived Table (this could also be done for a MERGE)

UPDATE Staging.CDD_Today

SET ABC_Risk = b.riskfactor

FROM

 ( SELECT .... FROM Staging.CDD_TodayCDD_CountryRisk a

   LEFT JOIN dbo.CDD_ABC_Uplift b ON b.REVIEW_ID = a.REVIEW_ID

   AND b.COUNTRY_NAME = a.ABC_Country

) as b

WHERE ... 

Dieter

Enthusiast

Re: Update statement with left outer join in from clause.

Hi Dieter,

This is from SQL server. I typed sql wrong while trying to remove some reference. It is actually

Update Staging.CDD_TodayCDD_CountryRisk

Set ABC_Risk =

Case When b.BRANCH_RISK_FACTOR = 'Undefined' Then a.ABC_Risk_wo_Override

 When b.BRANCH_RISK_FACTOR = 'Accepted' Then a.ABC_Risk_wo_Override

 When b.BRANCH_RISK_FACTOR Is NULL Then a.ABC_Risk_wo_Override

 Else Coalesce(b.BRANCH_RISK_FACTOR, a.ABC_Risk_wo_Override)

 End

From Staging.CDD_TodayCDD_CountryRisk a

Left Join dbo.CDD_ABC_Uplift b On b.REVIEW_ID = a.REVIEW_ID

 and b.COUNTRY_NAME = a.ABC_Country

a.ABC_country has null values.

Thanks

Enthusiast

Re: Update statement with left outer join in from clause.

Hi,

Below is one conversion, I am looking for generic template to be applied across SQL server code and that is the reason that update statement is not broken into multiple statements for left join. Please let me know if I am missing some future data issues with this code.

UPDATE a
FROM TAC_CDD_TodayCDD_CountryRisk a,
(SELECT b.BRANCH_RISK_FACTOR,a.ABC_Risk_wo_Override, a.REVIEW_ID,a.ABC_Country, a.Client_GID
FROM TAC_CDD_TodayCDD_CountryRisk a
LEFT JOIN TAC_CDD_ABC_Uplift b ON b.REVIEW_ID = a.REVIEW_ID
 AND b.COUNTRY_NAME = a.ABC_Country) M
 
SET ABC_Risk =
CASE WHEN M.BRANCH_RISK_FACTOR = 'Undefined' THEN a.ABC_Risk_wo_Override
 WHEN M.BRANCH_RISK_FACTOR = 'Accepted' THEN a.ABC_Risk_wo_Override
 WHEN M.BRANCH_RISK_FACTOR IS NULL THEN a.ABC_Risk_wo_Override
 ELSE COALESCE(M.BRANCH_RISK_FACTOR, a.ABC_Risk_wo_Override)
 END
 WHERE
COALESCE(m.Client_GID,'')  = COALESCE(a.Client_GID ,'')
AND  COALESCE(m.ABC_Country,'')  = COALESCE(a.ABC_Country,'')
   AND m.REVIEW_ID = a.REVIEW_ID 

Thanks

Harpreet

Senior Apprentice

Re: Update statement with left outer join in from clause.

Hi Harpreet,

i don't know if this is correct, i never used an outer join for update in SQL Server.

But there's no join condition on Client_GID in your original query.

Dieter

Enthusiast

Re: Update statement with left outer join in from clause.

Thanks Dieter, We have outer join used for mostly all updates and deletes on table. Client_gid is primary index for table and it is used along with other two joins for alias m. main purpose of all these 3 joins is to join rows from derived table having left outer join and table to be updated with 1-1 row match and no update doen by multiple rows from derived table.

N/A

Re: Update statement with left outer join in from clause.

Hi Guys,

I need some help in updating a table based another table column value:

Large tables:

Row count in Entry table 369,485,358

Row count in Entry_Line table 1,191,793,662

UPDATE BDWDEV2T_Stg.ST001_TRX_ACCOUNT_ENTRY_LINE

SET PRICETYPEUSED = CASE

WHEN OVERRIDEPRICE IS NOT NULL

THEN 'PriceOverride'

WHEN COMPETITORPRICE IS NOT NULL

THEN 'PriceMatch'

WHEN PROMOTIONPRICE IS NOT NULL

THEN 'Promotion'

WHEN TESOPRICE IS NOT NULL

THEN 'TESO'

WHEN  ACCOUNTNUMBER IS NOT NULL (from BDWDEV2T_Stg.ST001_TRX_ACCOUNT_ENTRY)

THEN 'Commerical'

ELSE 'Retail'

END

I need update BDWDEV2T_Stg.ST001_TRX_ACCOUNT_ENTRY_LINE based on the column ACCOUNTNUMBER IS NOT NULL from another table ( BDWDEV2T_Stg.ST001_TRX_ACCOUNT) and rest of the case columns are from BDWDEV2T_Stg.ST001_TRX_ACCOUNT_ENTRY_LINE

Key column in both table is ACTIONID and ACCOUNTENTRY

Need update all the rows in BDWDEV2T_Stg.ST001_TRX_ACCOUNT_ENTRY_LINE

Thanks,

Prax