SingleStore Managed Service

ERROR 1706 (HY000): Leaf Error (127.0.0.1:3307): Feature INSERT IGNORE … ON DUPLICATE KEY UPDATE is not supported by MemSQL

Issue

When you use the IGNORE modifier in a INSERT statement, SingleStore ignores records with duplicate key entries and only inserts records with unique key values. The ON DUPLICATE KEY UPDATE clause, instead of ignoring the insert, performs an update on the row with the duplicate key value. Using both of them in same query leads to a dilemma: whether to ignore the insert or update the record. To prevent any ambiguity in data, SingleStore returns an error, instead of choosing one operation over another.

Solution

Either use the IGNORE modifier or ON DUPLICATE KEY UPDATE clause in INSERT statements, but not both. For more information, see INSERT