SQL on duplicate of 2 values set 3rd value

Dor Birendorf 0
Viewed 38

I'm using a SQL table that represents what account can use which currencies, it looks like this:

id(pk) accountNumber currency active
1 1234 USD 0
2 1234 EUR 1
3 7777 USD 1

the meaning is that account '1234' can use EUR only (USD is not active).

I want to create an SQL statement that adds new lines to the table(to add new currencies to the system or unable account to use a currency), but in case the 'accountNumber - currency' pair already exists, change the 'active' column to active instead of adding a new line.

what is the best way to to that ? should I use If in this statement of "on duplicate"?

Thanks

mysql sql
1 Answers

One of the ways you can achieve this by executing a query like this:

BEGIN

DECLARE @accountNumber as int, @currency as nvarchar(10), @active as bit;
SET @accountNumber = 1234
SET @currency = 'USD'
SET @active = 1

IF NOT EXISTS (SELECT * FROM [AccountInfo] WHERE accountNumber = @accountNumber 
AND currency = @currency) 
   INSERT INTO [AccountInfo] (accountNumber,currency,active) 
   VALUES (@accountNumber,@currency,@active)
ELSE
   UPDATE [AccountInfo] SET active= @active where accountNumber = @accountNumber 
AND currency = @currency

END
Zeeshan Shoukat Abbasi0
Related