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');

Lukasz Szozda · Answer 1 · 2022-03-08T18:12:35.440

3

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;

edited Mar 08 '22 at 18:12

answered Aug 15 '17 at 08:56

Lukasz Szozda

162,964
23
234
275

i tried "on duplicate key update" but unfortunatly it only relates to the primary key which is the ticker and i do not know how to relate it to an index based on all 4 columns. – Stefan Müller Aug 15 '17 at 09:01
`UPDATE valuation SET value =14999260.46 WHERE ticker = 'BK001EUR' AND src_id =2 AND depot_id = 1 AND valuation_date ='2009-09-09'); INSERT INTO valuation (ticker,depot_id,src_id,valuation_date,value) SELECT 'BK001EUR',1,2,'2009-09-09',14999260.46 WHERE NOT EXISTS (SELECT 1 FROM valuation WHERE ticker = 'BK001EUR' AND depot_id = 1 AND src_id = 2 AND valuation_date = '2009-09-09'); ` – Stefan Müller Aug 15 '17 at 09:37
You forgot to add `FROM DUAL` before your where not exists line. – Kevin Mar 08 '22 at 16:18
@Kevin The dual is not mandatory: [db<>fiddle demo](https://dbfiddle.uk/?rdbms=mariadb_10.6&fiddle=a2b13d3011e1272a99ecabca7ceaed93) starting from MariaDB 10.4, but I upated my answer for completness – Lukasz Szozda Mar 08 '22 at 18:12

score 3 · Answer 2 · edited Dec 23 '19 at 16:13

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.

Dieter R · Answer 3 · 2022-01-03T20:45:47.027

I tried a similar INSERT-statement, which should not insert the data, if a record with the same values exists. My statement didn't work until I added 'FROM DUAL':

INSERT INTO measurement
    (sensorid, parameterid, measuredatetime, measure)
    SELECT sensoridIn,parameteridIn,measuredatetimeIn,measureIn
    FROM DUAL
    WHERE NOT EXISTS (SELECT *
          FROM measurement
          WHERE sensorid = sensorId
          AND measuredatetime = measuredatetimeIn
          AND measure = measureIn
          AND parameterid = parameteridIn);

For more info see https://mariadb.com/kb/en/dual/ and How can I do 'insert if not exists' in MySQL?

IF NOT EXISTS - Mariadb Syntax

3 Answers3