I have the following query:
INSERT INTO tabla (
`A`,
`B`,
`C`,
`D`,
`E`,
`lastupdated`
)
VALUES
'1', '1231', '99.99', '', '', NOW()
ON DUPLICATE KEY UPDATE
`lastupdated` = IF(
`C` != VALUES(`C`) OR
`D` != VALUES(`D`) OR
`E` != VALUES(`E`),
NOW(),
`lastupdated`
),
`C` = VALUES(`C`),
`D` = VALUES(`D`),
`E` = VALUES(`E`)
What I want to achieve is that the field is updated lastupdated
only when the other fields have been modified, otherwise I want it to remain with the current date (unmodified).
The table has a PK composed of (`A`, `B`)
. Therefore, when A
and exist, the , , and B
fields are updated . So far everything works perfectly, but, the part in which I compare the current values with the ones that are being inserted to decide whether or not to use it doesn't seem to work:C
D
E
NOW()
`lastupdated` = IF(
`C` != VALUES(`C`) OR
`D` != VALUES(`D`) OR
`E` != VALUES(`E`),
NOW(),
`lastupdated`
),
The field lastupdated
is never modified to the current date of NOW()
even though I am manually modifying the field C
to force it.
Any idea what may be going on?
I finally decided to take another path. I've modified the field
lastupdated
to be of typeTIMESTAMP
and automatically update when the record is edited:It works as expected and
lastupdated
only updates when it detects changes to theC
,D
and/or fieldsE
.The query is finally reduced to: