0

I have three columns: a, b, c.

I want to INSERT a new row if a, b, c do not already exist into a row.

Using Python to do this.

INSERT INTO mytable(column_a, column_b, column_c) 
VALUES(value_a, value_b, value_c) 
WHERE value_a, value_b, value_c are all unique in existing rows.
GMB
  • 188,822
  • 23
  • 52
  • 100
brainygrunt
  • 7
  • 5

2 Answers2

3

One way to approach this is to create a unique index on the three columns. The database will reject the insert if you attempt to insert a duplicate row. No special query is required. This should work on any relational database, not just MySQL.

Create the unique index with this:

CREATE UNIQUE INDEX a_b_c ON mytable (column_a, column_b, column_c)

Insert like this:

INSERT INTO mytable(column_a, column_b, column_c) VALUES (value_a, value_b, value_c)

If there already exists a row with the same values for columns column_a, column_b, and column_c the database will reject the insert and you will see an exception in Python.

mhawke
  • 75,264
  • 8
  • 92
  • 125
  • `insert IGNONE` may be usable here (if one do not need to report it back to user) – fifonik Feb 12 '19 at 22:36
  • @fifonik: sure, if that is acceptable, but OP didn't indicate that. – mhawke Feb 12 '19 at 22:37
  • keep in mind `INSERT IGONE` on the innoDB table engine will "waste" auto_increment values when the insert failed @fifonik – Raymond Nijland Feb 12 '19 at 22:38
  • Also `INSERT IGNORE` turns **all** errors into warnings, not just duplicate keys errors. This can be tricky. `ON DUPLICATE KEY UPDATE` is the way to go. – GMB Feb 12 '19 at 22:39
3

If you are fine with implementing a general mechanism to avoid duplicates on columns column_a/b/c, you can simply add a composite unique constraint on both columns, like

ALTER TABLE mytable 
ADD CONSTRAINT constr_ID UNIQUE (column_a, column_b, column_c);

If anything operation happens on the table that would generate duplicates, MySQL will raise a constraint violation error. You can ignore the error with the ON DUPLICATE KEY UPDATE option :

INSERT INTO mytable(column_a, column_b, column_c)
VALUES(value_a, value_b, value_c)
ON DUPLICATE KEY UPDATE column_a = column_a;

In this demo on DB Fiddle, we are inserting 3 records with 2 duplicates, and we end up with 2 records in the table, as expected.

On the other hand, if you want to restrict the check on duplicate to just one query, and/or if you want to avoid wasting autoincrement sequences on duplicate keys, then you can turn to an INSERT ... SELECT statement, with a WHERE NOT EXISTS condition that does the duplicate check :

INSERT INTO mytable(column_a, column_b, column_c)
SELECT src.*
FROM (SELECT value_a column_a, value_b column_b, value_c column_c) src
WHERE NOT EXISTS (
    SELECT 1 
    FROM mytable
    WHERE 
        column_a = src.column_a 
        AND column_b = src.column_b 
        AND column_c = src.column_c
);

If you attempt to insert a duplicate, the query will do nothing (and will not generate an error or warning).

Demo on DB Fiddle.

GMB
  • 188,822
  • 23
  • 52
  • 100
  • I tried out the first implementation, but nothing is getting inserted into my table. – brainygrunt Feb 12 '19 at 22:58
  • @brainygrunt : if you try to insert duplicates, then nothing gets inserted. Else, the record gets inserted. – GMB Feb 12 '19 at 23:00
  • Agreed. But nothing at all gets inserted. Since the table is currently empty, there should at least be one row inserted. – brainygrunt Feb 12 '19 at 23:01
  • @brainygrunt : see [this DB Fiddle demo](https://www.db-fiddle.com/f/cYesbAB9ntKoY6CLXJ1oQK/1) that demonstrates how the functionality works. – GMB Feb 12 '19 at 23:07
  • thanks! this works. however, my id autoincrement is skipping numbers like 1, 2, 6, 40, 121 etc. – brainygrunt Feb 12 '19 at 23:17
  • Welcome @brainygrunt. Yes `ON DUPLICATE KEY...` does waste autoincrement values. If that's a problem for you then you can consider switching to the second solution I provided... – GMB Feb 12 '19 at 23:19