Insert if it does not exist or Update if it already exists in Mysql?

Question

Insert if it does not exist or Update if it already exists in Mysql?

Navigation

#1 by (2 votes)
#2 by (1 votes)

3

How can I give MySql a INSERT or a UPDATE if the line in question already exists or not?

I tried the following but it did not work:

IF EXISTS (SELECT * FROM configs WHERE id_serie = :id) 
           UPDATE configs 
                SET dados = :valor  
                WHERE id_serie = :id 
ELSE 
    INSERT INTO configs (id_serie, dados) VALUES (:id, :valor)

But it did not work, I read about ON DUPLICATE KEY UPDATE but since the column I use to check in id_serie is not the primary key of the table, I figured it would not work, or does it work?

mysql

asked by anonymous 28.04.2017 / 16:59

2 answers

Why does the input work in Python 3.x and not in 2.7? Performance and security PHP x C ++ for web

score 2 · Answer 1

You can try to use REPLACE INTO . It is similar to INSERT , but if the record to be inserted has the same value as a primary key or a single index, the existing record is deleted and the new record is inserted.

REPLACE INTO configs (id_serie, dados) VALUES (:id, :valor)

If you'd like to take a look at the documentation: Mysql - REPLACE Syntax

score 1 · Answer 2

Try one of the forms below.

Assigning the total number of records found in a variable.

declare @total int;
set @total = (select count(*) from configs where id_serie = :id)

if (@total > 0)
    update anime_configs set dados = :valor 
    where id_serie = :id 
else 
    insert into configs (id_serie, dados) values (:id, :valor)

Or by checking the amount of affected records in update . If the update gives error (because it already exists) then zero records will be returned, and with that, it will call insert .

update anime_configs set dados = :valor where id_serie = :id
if (@@rowcount = 0)
    insert into configs (id_serie, dados) values (:id, :valor)