I am trying to use a WHERE condition on an INSERT INTO statement that contains ON DUPLICATE KEY. An example of what I'm trying to achieve is below.
This SQL statement is being called by third-party software and we want to exclude some exports based on numerical values, in this case 2. So, when X != Y perform INSERT, if X == Y, don't.
INSERT INTO satellites (name, intname) VALUES ('%SATELLITE.NAME%','%SATELLITE.INTNAME%') ON DUPLICATE KEY UPDATE name='%SATELLITE.NAME%',intname='%SATELLITE.INTNAME%' WHERE '%SATELLITE.NAME%' != 2;
Use a SELECT query rather than VALUES. Then you can add a condition to ignore the name
INSERT INTO satellites (name, intname)
SELECT '%SATELLITE.NAME%' AS name, '%SATELLITE.INTNAME%'
FROM DUAL
HAVING name != 2
ON DUPLICATE KEY UPDATE name = VALUES(name), intname = VALUES(intname)
Note that in the ON DUPLICATE KEY UPDATE clause you don't need to specify the assignment to the unique key column, since it's not going to change (since that's what is duplicate). But I didn't know which one is the unique key, so I didn't remove it.
DUAL is a placeholder for a table name when you're just selecting literal values.