1

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.

1 Answer 1

6

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. ...

3
  • 1
    This made may day. Been banging my head on this one for a few hours. May 1, 2020 at 17:02
  • 1
    Thanks for this..., I needed an answer to this exact question.
    – Dan
    Feb 2, 2022 at 21:42
  • As @ypercube stated, be careful about the ordering. If you need complex conditions, evaluate the whole condition first and then update, otherwise it will lead to inconsistent data. Dec 19, 2022 at 19:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.