Update statement

Question

Update statement

Vineet S 1,390

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'

NIKHILA NETHIKUNTA 4,610 Reputation points Microsoft External Staff

2024-09-24T18:32:28.0866667+00:00

@Vineet S
Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer accepted by question author

1 additional answer

Your answer

NIKHILA NETHIKUNTA 4,610 Reputation points Microsoft External Staff

2024-09-24T18:32:28.0866667+00:00

@Vineet S
Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

LiHongMSFT-4306 31,611

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".

NIKHILA NETHIKUNTA 4,610 Reputation points Microsoft External Staff

2024-09-26T02:30:10.23+00:00

@Vineet S Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

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