IF NOT EXISTS - Mariadb Syntax

Question

i tried to either insert a new row or update an existing one if it is already included.

I figured i could try it with indices but to be honest i am not sure how to do this hence i tried to do it with an IF statement. Unfortunatly this isn't working either because it says i have a problem with my Syntax. I am using MariaDB.

IF NOT EXISTS (SELECT * FROM valuation 
               WHERE ticker = 'BK001EUR' AND depot_id =1 
                 AND src_id =2 AND valuation_date ='2009-09-09')
INSERT INTO valuation (ticker,depot_id,src_id,valuation_date,value) 
VALUES ('BK001EUR',1,2,'2009-09-09',14999260.46) 
ELSE 
UPDATE valuation 
SET value =14999260.46 
WHERE ticker = 'BK001EUR' AND depot_id =1 
  AND src_id =2 AND valuation_date ='2009-09-09');

Answer 1

The proper way to do this is using insert ... on duplicate key update. I would write the query as:

INSERT INTO valuation (ticker, depot_id, src_id, valuation_date, value) 
  VALUES ('BK001EUR', 1, 2, '2009-09-09', 14999260.46) 
  ON DUPLICATE KEY UPDATE value = VALUES(value);

(Note the use of VALUES() so you don't have to repeat the input.)

For this to work, you need a unique index on the keys you care about:

create unique index unq_valuation_4 on valuation(ticker, depot_id, src_id, valuation_date);

The duplicate key does not need to be the primary key index.
It can be any unique index.

Answer 2

You could use:

-- if exists then it will update
UPDATE valuation 
SET value =14999260.46 
WHERE ticker = 'BK001EUR' 
  AND depot_id =1 AND src_id =2 AND valuation_date ='2009-09-09';

-- if not exist then insert
INSERT INTO valuation (ticker,depot_id,src_id,valuation_date,value)
SELECT 'BK001EUR',1,2,'2009-09-09',14999260.46
-- FROM dual
WHERE NOT EXISTS (SELECT 1 
                  FROM valuation 
                   WHERE ticker = 'BK001EUR' 
                     AND depot_id =1 
                     AND src_id =2 AND valuation_date ='2009-09-09');

db<>fiddle demo

Or better way INSERT ON DUPLICATE UPDATE:

INSERT INTO valuation (ticker,depot_id,src_id,valuation_date,value) 
VALUES ('BK001EUR',1,2,'2009-09-09',14999260.46)
ON DUPLICATE KEY UPDATE value =14999260.46;