INSERT ... ON DUPLICATE regular non primary key column do nothing

The basics, if you want to do a row insert in MySQL/MariaDB, the syntax is:

INSERT INTO table (
	column1,
	column2...
) VALUES(
	'value1',
	'value2' ...
);

If you want to insert if a primary key column value does not already exist then you can do this by setting a column value to the same as it is for duplicates:

INSERT INTO table (
	column1,
	column2...
) VALUES(
	'value1',
	 'value2' ...
) ON DUPLICATE KEY UPDATE column1=column1;

The syntax should have been something like "ON DUPLICATE KEY DO NOTHING" to avoid these kind of workarounds, but it does not exist.

This does however only work if the column to check is a primary key column, or sometimes(!) if an index has been created for it.

But what if you want to insert if a regular non-primary key column value is not found in the table?

There are several ways to do this, none are easy, they are explained here:
https://stackoverflow.com/questions/39758422/mysql-insert-only-if-a-condition-is-true

Here is the compiled suggestion from that:

INSERT INTO table (
	column1,
	column2
) SELECT
	'value-to-insert-1',
	'value-to-insert-2'
WHERE NOT EXISTS(
	SELECT
		column_to_check
	FROM
		table
	WHERE
		name = 'value-to-insert-1'
);

Here the SELECT provides the values for the INSERT based on a WHERE check that looks in another table. The syntax here could have been ON <whatever condition> for INSERT queries to avoid these kind of workarounds.

This is a personal note. Last updated: 2022-04-01 18:15:53.



LinkedIn
Klebe.se

Don't forget to pay my friend a visit too. Joakim