插入一个新的(如果不存在的话)，并更新(如果存在的话) [英] INSERT a new if it doesn't exist, and UPDATE if it does

问题描述

如果不存在，我需要在BD上插入新记录，如果存在，则需要更新.为此，我使用2个查询:

I need to insert a new record on BD if it doesn't exist, and update if it does. For this, I use 2 queries:

INSERT  INTO tbl_ponto ( `rodada`, `ponto`, `patrimonio`, `FK_loginID` ) VALUES ( '10', 100.00, 100.00, 3 );
UPDATE tbl_ponto SET ponto=150.00, patrimonio=150.00 WHERE rodada=10 AND FK_loginID=3;

我只用1个查询就可以做到这一点.我了解了有关REPLACE的信息，但无法使其适用于我的情况. 你能帮我吗?

I would do this with just 1 query. I read about REPLACE but could not make it work in my case. Could you please help me?

推荐答案

INSERT INTO tbl_ponto (`rodada`, `ponto`, `patrimonio`, `FK_loginID`)
    VALUES ('10', 100.00, 100.00, 3)
ON DUPLICATE KEY UPDATE ponto=150.00, patrimonio=150.00;

由于某些值与已经存在的PRIVATE/UNIQUE KEY冲突而导致INSERT失败时，将调用触发器ON DUPLICATE KEY.

The trigger ON DUPLICATE KEY is called when the INSERT fails because some of the values conflict with a PRIVATE/UNIQUE KEY that already exists.

在这种情况下，rodada, FK_loginID必须为PRIVATE KEY或UNIQUE KEY，否则您将不得不更新这些值之一.

In this case, rodada, FK_loginID must be PRIVATE KEY or UNIQUE KEY, otherwise you will have to update one of those values.

这篇关于插入一个新的(如果不存在的话)，并更新(如果存在的话)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！