CODEWITHSUNDEEP
sqlmariadbnot-exists
James Dumais
James Dumais

Reputation: 11

SQL IF NOT EXISTS INSERT ELSE UPDATE on mariadb

I am trying to do a if not exist insert else update, however it's telling me there's an issue with insert:

Failed to execute SQL : SQL IF NOT EXISTS (SELECT * FROM vendor_publication_daily_draws WHERE pub_id = '73609850' AND paper_code = 'STSC') THEN INSERT INTO vendor_publication_daily_draws (`alouette.acct`, month, year, paper_code, pub_id, `01`) VALUES ('97', '08', '2022', 'STSC', '73609850', '5'); ELSE UPDATE vendor_publication_daily_draws SET `01` = '5' WHERE `alouette.acct` = '97' AND (pub_id = '73609850' OR pub_id = '73609850') AND paper_code = 'STSC' AND month = '08' AND year = '2022'; failed :
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1

What am I doing wrong? The table does not have a usable unique key as the data is very repetitive.

This is what I have:

IF NOT EXISTS (
               SELECT * FROM vendor_publication_daily_draws 
               WHERE pub_id = '73609850' AND paper_code = 'STSC') 
THEN
INSERT INTO vendor_publication_daily_draws (`alouette.acct`, month, year, paper_code, pub_id, `01`) 
VALUES ('97', '08', '2022', 'STSC', '73609850', '5') 
ELSE 
UPDATE vendor_publication_daily_draws 
SET `01` = '5' 
WHERE `alouette.acct` = '97' 
AND (pub_id = '73609850' OR pub_id = '73609850') 
AND paper_code = 'STSC' 
AND month = '08' 
AND year = '2022';

I've looked everywhere and cannot seem to find a working example that matches my needs.

Upvotes: 0

Views: 2171

Answers (1)

nbk
nbk

Reputation: 49410

This assumes that you have an UNIQUE INDEX or PRIMARY KEY on (pub_id ,paper_code )

INSERT INTO vendor_publication_daily_draws (`alouette.acct`, month, year, paper_code, pub_id, `01`) 
VALUES ('97', '08', '2022', 'STSC', '73609850', '5') 
  ON DUPLICATE KEY UPDATE month=VALUES(month),`alouette.acct` = VALUES(`alouette.acct`)
  ,year = VALUES(year),`01` = VALUES(`01`)

there is also the possibility of a dynamic approach, which i also can provide if abouve fails.

Upvotes: 1

Related Questions