如果存在两个相同的字段，则插入else更新 [英] If two same fields exists insert else update

问题描述

假设我有two个字段a和b.

现在让我们说我想编写一个查询，如果a = 100 AND b = 120会更新，否则它将创建新字段.

Now let's say i want to write a query which would update if a = 100 AND b = 120 else it would create new field.

也a和b必须彼此唯一，我的意思是它可以是等于100的两个a或等于200的两个b，但是可以不再是

Also a and b have to be unique to each other, what i mean is that it can be two a equals to 100 or two b equals to 200, but there can't be more fields like

a = 100和b = 120，希望您理解我的意思.

a = 100 and b = 120, i hope you get what i mean.

推荐答案

如果对(a,b)组合具有UNIQUE约束，则可以使用

If you have a UNIQUE constraint on the (a,b) combination, you can use the INSERT ... ON DUPLICATE KEY UPDATE ... syntax for this functionality. Examples:

INSERT INTO TableX
  ( a, b, c, d, e)
VALUES
  ( 100, 200, 1, 2, 3)
ON DUPLICATE KEY UPDATE
  c = VALUES(c)
, d = VALUES(d)
, e = VALUES(e)

INSERT INTO TableX
  ( a, b, c, d, e)
VALUES
  ( 100, 200, 1, 7, 20)
ON DUPLICATE KEY UPDATE
  c = c + 1
, d = d + 1
, e = e + 1 

这篇关于如果存在两个相同的字段，则插入else更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！