Insert if doesn't exist fail if table empty

Question

I used the following command to avoid duplicates in a table :

INSERT INTO mytable (num,name)
SELECT 2,'example' FROM mytable WHERE NOT EXISTS
(SELECT * FROM mytable WHERE num=2 AND name='example') LIMIT 1;

It is working but NOT if mytable is empty.

mytable also contain a AUTO_INCREMENT id.

CREATE TABLE mytable (
 id int(11) NOT NULL auto_increment,
 num int(11) NOT NULL,
 name varchar(100) NOT NULL,
 PRIMARY KEY (id)
);

Do you recommanded another method or a workaround ?

Is this a one-off, or does your table have a _requirement_ that the combination of `num,name` always be unique? If so, you need to create a `UNIQUE` index on that pair of columns. — Michael Berkowski, Oct 11 '16 at 13:35

score 0 · Answer 1 · answered Oct 11 '16 at 13:52

0

In my case replacing mytable by DUAL did the trick. (But i have no idea why)

INSERT INTO mytable (num,name)
SELECT 2,'example' FROM mytable WHERE NOT EXISTS
(SELECT * FROM mytable WHERE num=2 AND name='example') LIMIT 1;

Replaced by :

INSERT INTO mytable (num,name)
SELECT 2,'example' FROM DUAL WHERE NOT EXISTS
(SELECT * FROM mytable WHERE num=2 AND name='example') LIMIT 1;

Thanks for the help.

answered Oct 11 '16 at 13:52

bob dylan

889
1
9
21

Reason: `SELECT FROM mytable WHERE ` will return *no* records when `mytable` is empty. Using `dual`, you can also omit that `LIMIT 1` because `dual` has only one record. – JimmyB Oct 11 '16 at 14:10

score 0 · Answer 2 · answered Oct 11 '16 at 14:48

You can create Unique column by using MySQL UNIQUE

Just update your CREATE TABLE query like below

CREATE TABLE mytable (
  id int(11) NOT NULL auto_increment,
  num int(11) NOT NULL,
  name varchar(100) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE (num) # You can define Unique column like this
);

Note: Then you no need to check Unique value when Save to Database.

And try to use normal SQL query for inserting Data

INSERT INTO mytable (num,name) VALUES(2,'example')

score -1 · Answer 3 · answered Oct 11 '16 at 13:36

-1

You can do something like Select * from tbl where _____ = ______

Then add if statement if results are available then insert or otherwise leave

answered Oct 11 '16 at 13:36

M.suleman Khan

501
4
17

score -1 · Answer 4 · answered Oct 11 '16 at 13:38

-1

Why do not you simply use INSERT IGNORE. Also why do you want to copy the data from same table?

answered Oct 11 '16 at 13:38

Gopal

11
3

Insert if doesn't exist fail if table empty

4 Answers4