CODEWITHSUNDEEP
mysqlon-duplicate-key
Arbiter
Arbiter

Reputation: 504

INSERT ... ON DUPLICATE KEY UPDATE [IF]

This query obviously won't work as it's a blend of MySQL and PHP, It's simply to explain, in readable-ish terms, what I'm aiming for:

INSERT INTO table 
    (userid, courseid, valid_days, valid_to) 
VALUES 
    ('1', '1', '28', '1401732028') 
ON DUPLICATE KEY UPDATE 
    (If the existing records valid_to == 0 || NULL){ 
         //then add the value of valid_days to the existing records valid_days field
    }else{
         //then turn the submitted valid_days (28) into seconds and add it to the existing valid_to value
    }

I hope this makes sense, and sorry for using PHP and MySQL in a scary way to explain an issue :)

Thank you!

Upvotes: 1

Views: 2239

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You can use if() and case in the logic for on duplicate key update. I may not understand the purpose for doing this, but I think the following implements it:

INSERT INTO table (userid, courseid, valid_days, valid_to) 
    VALUES ('1', '1', '28', '1401732028') 
    ON DUPLICATE KEY UPDATE 
        valid_days = (case when valid_to = 0 or valid_to is null
                           then valid_days + values(valid_days)
                           else valid_days
                      end),
        valid_to = (case when valid_to > 0
                         then valid_to + values(valid_days)*60*60*24
                         else valid_to
                    end);

Upvotes: 3

Related Questions