How can I Loop a column value if Row exists using INSERT ON KEY DUPLICATE UPDATE?

Question

In this Table Scheme

[id  -  name  -  active]
[1   -  alex  -  0     ]

I have a UNIQUE KEY on name, active, What I'm trying to do is to INSERT a new Row

[2 - max - 1 ]

using INSERT INTO table (name, active) VALUES (max, 1) ON KEY DUPLICATE active = 0

That I want to loop at active column value,

If the row:[2-max-1] exists with active=1 to change active value to 0

And if exists with active=0 to change it to 1,

And if not exist to INSERT the row with active=1

I keep thinking but can't figure out a way to make this loop using only INSERT . . . ON KEY DUPLICATE UPDATE

Answer 1

The simple expression which returns what you want is (NOT active).

So, your insert will become (you missed the UPDATE keyword):

INSERT INTO table 
    (name, active) 
VALUES 
    ('max', 1) 
ON DUPLICATE KEY UPDATE
    active = NOT active ;

---

If you want to have only one row per name, then you need to change the UNIQUE constraint to (name) only. With that change, the above insert will do exactly what you want:

• Insert the row except if a row with that name already exists, in which case toggle the existing active value (essentially ignoring the 1 / 0 in the insert VALUES).