Help with an update query please

Hi, I have a couple of tables, Cash and FXRates. The Cash table gets populated daily via SSIS package import from CSV but column BaseCurrencyValue is always NULL.

I am looking for help on how to write an update query that will update the BaseCurrencyValue column for all records based on a lookup where Cash.Currency = FXRates.ToCurrency and returns the FXRate value which I then need to multiply against the Cash.Value field before updating that value into the BaseCurrencyValue field.
It's a simple currency conversion.

Here are the basic tables and data:

CREATE TABLE Cash(
[SaleDate] varchar NULL,
[SaleTime] varchar NULL,
[Receipt No] [int] NULL,
[Currency] varchar NULL,
[Value] [decimal](18, 2) NULL,
[BaseCurrencyValue] [decimal](18, 2) NULL
)

GO

CREATE TABLE FXRates(
[BaseCurrency] varchar NULL,
[ToCurrency] varchar NULL,
[FXRate] [decimal](18, 8) NULL
)

GO

INSERT INTO Cash
([SaleDate]
,[SaleTime]
,[Receipt No]
,[Currency]
,[Value]
,[BaseCurrencyValue])
VALUES
('20/08/2019'
,'09:10:00'
,1
,'GBP'
,20
,NULL),
('19/08/2019'
,'09:10:00'
,1
,'EUR'
,20
,NULL)
GO

INSERT INTO FXRates
([BaseCurrency]
,[ToCurrency]
,[FXRate])
VALUES
('EUR'
,'GBP'
,1.11110000),
('EUR'
,'EUR'
,1)
GO

Any help would be much appreciated as I have tried triggers and update queries to no avail.
Thank you.

Try:

UPDATE C
SET BaseCurrencyValue = X.BaseCurrencyValue
FROM dbo.Cash C
	JOIN dbo.FXRates R
		ON C.Currency = R.ToCurrency
	CROSS APPLY
	(
		VALUES(ROUND(C.[Value] * R.FXRate, 2))
	)  X (BaseCurrencyValue)
WHERE COALESCE(C.BaseCurrencyValue, -1) <> X.BaseCurrencyValue;

ps Always specify the length of a varchar otherwise you will start to get all sorts of 'interesting bugs'.

1 Like

Wow! Never even seen CROSS APPLY or COALESCE. It kind of worked but I probably should have mentioned that I have some negative values in C.value field which all work except when it is -1.00. That either fails or converts to a NULL not sure which.

Just choose another dummy value. Maybe 0.

Genius...thank you very much.