INSERT or UPDATE if the condition on two columns is satisfied (regardless of order)

Question

I want to do the following:

INSERT INTO table1 (term1, term2, number) values ('A','B',10); 

however, if the values ​​of A and B are already present table1 regardless of their order, i.e. predicate

(term1='A' && term2='B') OR (`term1='B' && term2='A')

is true, then I just want to update the column number. Is there any way to do this?

Answer 1

A (possibly) clean way to handle this situation is to use INSERT ... ON DUPLICATE KEY UPDATE, read the documentation .

ON DUPLICATE KEY UPDATE, ​​, UNIQUE PRIMARY KEY, MySQL UPDATE

UNIQUE. .

, , .

. , , .

Answer 2

@okiharaherbst,

, : "uniqueKey" , :

INSERT INTO table1(term1,term2,num,uniquekey) VALUES ( "a","b",10, 
concat(greatest(term1,term2),least(term1,term2))) on duplicate key update num=10;