Update statement

Question

How to convert query into update statement... Unable to do it

select rt.outstandingamount,rt.outstandingamount * TT_USD.RateMultiplier,ENTITY,FromCurrencyKeyFK,ConversionCurrencyKey,RateMultiplier

from deltalake.FactARReceiptDetail RT LEFT outer JOIN DeltaLake.DimCurrencyCode cur1 ON CASE WHEN TRIM(RT.currency) IN ('B-USD', 'Z-US$') THEN 'USD'

                                      WHEN TRIM(RT.currency) IN ('B-CAD', 'Z-C$') THEN 'CAD'

                                      WHEN TRIM(RT.currency) IN ('Z-CNY') THEN 'CNY' ELSE TRIM(RT.currency) END = cur1.CurrencyCode AND cur1.DeltaActive = TRUE

                                      LEFT OUTER JOIN DimTempCurrency TT_USD ON TT_USD.FromCurrencyKeyFK = trim(cur1.ConversionCurrencyKey)
```---left join DimTempCurrency b on a.currency=where InvoiceNumber = 'INV250430' 

where cur1.CurrencyKey='19' and ENTITY like '%RE%'--and  outstandingamount <>0 and invoicenumber='INVE049206'

and   RT.outstandingamount IS NOT NULL

   AND RT.outstandingamount <> '0'

Accepted Answer

Hi @Vineet S

How to convert query into update statement... Unable to do it

Here is the syntax of the UPDATE with JOIN clause:

UPDATE 
    t1
SET 
    t1.c1 = t2.c2,
    t1.c2 = expression,
    ...   
FROM 
    t1 [INNER | LEFT] JOIN t2 ON join_predicate
WHERE 
    where_predicate;

For better support, you could post the code you wrote or the error you encountered.

Best regards,

Cosmog

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer

Maybe this?

UPDATE rt 
SET    rt.outstandingamount = 99
from deltalake.FactARReceiptDetail RT 
LEFT outer JOIN DeltaLake.DimCurrencyCode cur1 ON CASE WHEN TRIM(RT.currency) IN ('B-USD', 'Z-US$') THEN 'USD'
                                      WHEN TRIM(RT.currency) IN ('B-CAD', 'Z-C$') THEN 'CAD'
                                      WHEN TRIM(RT.currency) IN ('Z-CNY') THEN 'CNY' ELSE TRIM(RT.currency) END = cur1.CurrencyCode AND cur1.DeltaActive = TRUE
                                      LEFT OUTER JOIN DimTempCurrency TT_USD ON TT_USD.FromCurrencyKeyFK = trim(cur1.ConversionCurrencyKey)
where cur1.CurrencyKey='19' and ENTITY like '%RE%'--and outstandingamount <>0 and invoicenumber='INVE049206'
and RT.outstandingamount IS NOT NULL
AND RT.outstandingamount <> '0'

In all seriousness, I don't know what you want to achieve in your UPDATE statement, so I had to consult my crystal ball.

Share via

Update statement

1 additional answer

Your answer