Insert into if not exists not inserting

Question

INSERT INTO my_table (field_1, field_2)
SELECT val_1, val_2     
FROM my_table
WHERE NOT EXISTS (SELECT field_1
                        FROM my_table
                        WHERE field_2 = val_2)
LIMIT 1

I can not use unique index on field_2 field. I'm trying to insert if not exists a tuple with field2 = val_2. Without the "where" clause, this insert. With the "where" clause EVEN WHEN EMPTY TABLE, it won't insert.

Any help on that?

Of course it won't insert when the table is empty, you'd be selecting from an empty table. Try selecting from `DUAL` instead. _I'm assuming `val_1` and `val_2` are placeholders for literal values, otherwise the query wouldn't make any sense._ — Uueerdo, Jun 06 '19 at 17:49

score 0 · Answer 1 · answered Jun 06 '19 at 17:44

0

Solution is that check if key exist:

INSERT INTO my_table (field_1, field_2) ON DUPLICATE KEY INSERT IGNORE.

this question maybe useful.

answered Jun 06 '19 at 17:44

Morteza Jalambadani

2,190
6
21
35

score 0 · Accepted Answer · answered Jun 06 '19 at 17:46

0

I guess val_1 and val_2 are not columns of the table, right?
They are values that you want to insert in the table.
So drop:

FROM my_table

and use:

INSERT INTO my_table (field_1, field_2)
SELECT val_1, val_2     
WHERE NOT EXISTS (
  SELECT field_1
  FROM my_table
  WHERE field2 = val_2
)

answered Jun 06 '19 at 17:46

forpas

160,666
10
38
76

Thanks. You are a genius May you explain why it does not work in the other case? – voskys Jun 06 '19 at 17:56
1

Your version with `FROM my_table`, returns as many rows as there are in my_table when not exists is true. So if there are 100 rows the insert statement would attempt to insert 100 identical rows. I guess in your table maybe this is not allowed, right? – forpas Jun 06 '19 at 18:01
1

When the table is empty, your statement returns 0 rows so nothing is inserted. – forpas Jun 06 '19 at 18:04
curious thing is this: SELECT val_1, val_2 FROM my_table WHERE NOT EXISTS (SELECT field_1 FROM my_table WHERE field_2 = val_2) LIMIT 1 This part returns correctly val_1, val_2. that is why I do not understand that. – voskys Jun 06 '19 at 18:06
I did not notice limit 1 at the end. Now if the table is empty your statement would **not** work for sure because FROM my_table returns nothing. If it is not empty, it should work when not exists returns true. – forpas Jun 06 '19 at 18:13

score 0 · Answer 3 · answered Jun 06 '19 at 17:47

First create a unique index on the column or columns that you don't want duplicated. I cannot tell if it is on both or just field_2:

create unique index unq_my_table_field_2 on my_table(field_2);

Then ignore the error using on duplicate key update:

INSERT INTO my_table (field_1, field_2)
    VALUES (val_1, val_2)
    ON DUPLICATE KEY UPDATE field_2 = VALUES(val_2);   -- this is a no-op, because the value is already the same

The fact the your code doesn't work on an empty table has nothing to do with the WHERE clause but with using from my_table in the from clause. If there are no rows, then the query returns . . . no rows. No surprise there that nothing gets inserted.

Insert into if not exists not inserting

3 Answers3