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

Asked

Viewed 2,114 times

3

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

I tried it this way but it didn’t 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 didn’t work, I read about ON DUPLICATE KEY UPDATE but as the column I use to verify in the case id_serie is not the primary key of the table, I imagined it would not work, or works?

mysql

3 answers

2

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

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

If you want to look at the documentation: Mysql - REPLACE Syntax

  • But in this case, if I change only one column, the new record will get all the values from the other old columns and insert into the new one updating only what I need?

  • No. If you leave a column without informing, you will lose the value of these fields.

  • It doesn’t seem very safe to do this way, I think I’ll end up having to combine a little PHP with this UPDATE / INSERT

1

Try one of the ways below.

Assigning total 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 records affected in the update. If the update gives error (because it already exists) then zero records will be returned, and with that, will call the insert.

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

  • Does this work for Mysql? I tested both here and the error

  • And what mistake in both?

  • Have you tried this (ps: I don’t know if it works)? IF(INSERT INTO configs (dados) 
 SELECT :valor WHERE NOT EXISTS (SELECT 1 FROM config WHERE id_serie = :id)) 
ELSE
 UPDATE configs SET dados = :valor WHERE id_serie = :id;

0

mysql has a parameter that checks if the record exists and if it exists you can update it. Follow the example.

INSERT INTO cliente (id, nome) VALUES (10, 'nome do cliente') ON DUPLICATE KEY UPDATE nome = 'nome do cliente';

you can update multiple fields by separating by "," Comma. the only obligation is the single key (Primary key).

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.