How can I make this sql INSERT SELECT query work on an empty table?

Question

I have the following query, that works on both MySQL and MariaDB:

INSERT into some_table (column1, column2, column3, column4, column5) 
SELECT  'val1', 'val2', 'val3', 'val4', 'val5'
FROM `some_table`
WHERE NOT EXISTS (
  SELECT `column1`, `column2` from `some_table`
  WHERE `column1` = 'val1' and `column2` = 'val2'
) limit 1`

It checks if a row exists based on unique values for val1 and val2: If the row exists, the query does nothing. If the row doesn't exist, the query inserts a new row with the values of val1, val2, val3, val4 and val5.

The query works, unless the table is empty, in which case, no value is being inserted.

How can I modify this query to also work with an empty table?

(Note: I cannot create a unique index on this table)

Barmar Barmar · Accepted Answer · 2018-08-13T21:22:15

Don't select from the table itself, select from the special table DUAL.

INSERT into some_table (column1, column2, column3, column4, column5) 
SELECT  'val1', 'val2', 'val3', 'val4', 'val5'
FROM DUAL
WHERE NOT EXISTS (
  SELECT `column1`, `column2` from `some_table`
  WHERE `column1` = 'val1' and `column2` = 'val2'
)

DUAL is a special dummy table name that can be used when you don't need to extract data from a real table.

Another option would be to create a unique index on (column1, column2) and then use INSERT IGNORE. If you try to create a duplicate, it will be skipped silently.

ALTER TABLE some_table ADD UNIQUE INDEX (column1, column2);
INSERT IGNORE into some_table (column1, column2, column3, column4, column5) 
VALUES ('val1', 'val2', 'val3', 'val4', 'val5')

How can I make this sql INSERT SELECT query work on an empty table?

1 Answers