Mysql – INSERT ON DUPLICATE KEY UPDATE with IF

MySQL

I have sy_version table which has a 2-column primary key: (mod_id, sub_mod):

CREATE TABLE `sy_version` (
`mod_id` VARCHAR(2) NOT NULL,
`sub_mod` VARCHAR(30) NOT NULL,
`version` VARCHAR(50) NULL DEFAULT NULL,
`remark` VARCHAR(50) NULL DEFAULT NULL,
`update_date` DATETIME NULL DEFAULT NULL,
`download_link` VARCHAR(50) NULL DEFAULT NULL,
`file` BLOB NULL,
PRIMARY KEY (`mod_id`, `sub_mod`)
)COLLATE='utf8_general_ci'
ENGINE=MyISAM;

And my data is

CS;sbm_sl.exe;2015.11.01;IBS Sales App;2015-11-10 11:34:13;\N;0x73002E00000000

I want to update my data using insert into on duplicate key, for the first it is working fine. But everytime I executed I get my file column become NULL, result that I want is : file becomes NULL when version where I want to set is bigger the present(original) version. And the command is like below:

INSERT INTO sbm_sys.sy_version (mod_id,sub_mod,`version`,remark,update_date,file) 
values ('CS','sbm_sl.exe','2015.11.07.1','IBS Sales App','2015-11-10 11:34:13', NULL) 
ON DUPLICATE KEY UPDATE `version`='2015.11.07.1', remark='IBS Sales App', 
update_date='2015-11-10 11:34:13', file=if(VALUES(version)>version,null,file) ;

It doesn't work fine, file column does not become NULL but the other columns is updated, so I tried changing the part become, and it works as I hoped:

file=if(VALUES(version)>version,null,file) ==> file=if(VALUES(version)>'2015.11.01',null,file) ;

The question is how I can't get "2015.01.01" from original version value?

Thanks for advice.

Best Answer

I think you should try this change:

INSERT INTO sbm_sys.sy_version
    (mod_id, sub_mod, version, remark, update_date, `file`) 
VALUES 
    ('CS', 'sbm_sl.exe', '2015.11.07.1', 'IBS Sales App', '2015-11-10 11:34:13', NULL)
ON DUPLICATE KEY UPDATE 
    `file` = IF(VALUES(version) > version, NULL, `file`),    -- first this column
    version = VALUES(version),                               -- then this one
    remark = VALUES(remark), 
    update_date = VALUES(update_date) ;

The order of the columns matters, rather unfortunately and in contrast with the SQL standard. The MySQL UPDATE documentation states:

The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL.

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

Single-table UPDATE assignments are generally evaluated from left to right. ...

Related Question