Avoid updating column if set to 1

Question

I have this query:

INSERT INTO user_list (USER_ID,USERNAME,NAME,ACTIVITY,PRIVATE)
VALUES(?,?,?,1,?) 
ON DUPLICATE KEY UPDATE USERNAME=?, NAME=?, ACTIVITY=ACTIVITY+1,PRIVATE=?

PRIVATE can be 0 or 1. I need to insert it according to a parameter, but if PRIVATE is already 1 on that row, it shouldn't be set as 0.

Basically if it turns 1, it can never become 0

How can I do this in a single query?

Answer 1

You can set the value of the column PRIVATE:

PRIVATE = PRIVATE OR VALUES(PRIVATE)

which will retain the value 1 if this is the original value, or change it to the new value if the original value is 0:

INSERT INTO user_list (USER_ID, USERNAME, NAME, ACTIVITY, PRIVATE)
VALUES(?, ?, ?, 1, ?) 
ON DUPLICATE KEY UPDATE USERNAME = VALUES(USERNAME), 
                        NAME = VALUES(NAME), 
                        ACTIVITY = ACTIVITY + 1, 
                        PRIVATE = PRIVATE OR VALUES(PRIVATE)