MySQL: Insert record if not exists in table


CREATE TABLE `table_name` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `tele` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

Insert a record:

INSERT INTO table_name (name, address, tele)
SELECT * FROM (SELECT 'Nazir', 'Kolkata', '033') AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_name WHERE name = 'Nazir'
) LIMIT 1;

Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

SELECT * FROM `table_name`;

+----+--------+-----------+------+
| id | name   | address   | tele |
+----+--------+-----------+------+
|  1 | Nazir  | Kolkata   | 033  |
+----+--------+-----------+------+

Try to insert the same record again:

INSERT INTO table_name (name, address, tele)
SELECT * FROM (SELECT 'Nazir', 'Kolkata', '033') AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_name WHERE name = 'Nazir'
) LIMIT 1;

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

+----+--------+-----------+------+
| id | name   | address   | tele |
+----+--------+-----------+------+
|  1 | Nazir  | Kolkata   | 033  |
+----+--------+-----------+------+

Insert a different record:

INSERT INTO table_name (name, address, tele)
SELECT * FROM (SELECT 'Santosh', 'Kestopur', '044') AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_name WHERE name = 'Santosh'
) LIMIT 1;

Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

SELECT * FROM `table_name`;

+----+--------+-----------+------+
| id | name   | address   | tele |
+----+--------+-----------+------+
|  1 | Nazir  | Kolkata   | 033  |
|  2 | Santosh| Kestopur  | 044  |
+----+--------+-----------+------+
Posted in Mysql
12 comments on “MySQL: Insert record if not exists in table
  1. Ok, but why it don’t wanna to work?
    INSERT INTO candidates (candidate_id, type)
    SELECT * FROM ( SELECT :candidate_id, :type ) AS tmp
    WHERE NOT EXISTS (
    SELECT * FROM candidates WHERE candidate_id = :candidate_id AND type = :type
    ) LIMIT 1;

    • Try this
      INSERT INTO candidates (candidate_id, type) SELECT * FROM (SELECT value_of_candidate_id, value_of_type) AS tmp WHERE NOT EXIST (SELECT * FROM candidates WHERE candidate_id = value_of_candidate_id AND type = value_of_type) LIMIT 1;

  2. Joosep says:

    Thank you, it worked on first time!;)

  3. Stopeck Raw says:

    please help, i spent time trying to solve this problem, the result i get is “#1060 – Duplicate column name ‘1’”. what am i doing wrong here.
    table is as follows
    ———+————+————–+———-+——–+————-+————-+-
    dis_id | dis_date | station_id | emp_id | level | discharge | remarks |
    ———+————+————–+———-+——–+————-+————-+-

    INSERT INTO dailydischarge( dis_date, station_id, emp_id, level, discharge, remarks )
    SELECT *
    FROM (

    SELECT ‘2014-09-03’, ‘1’, ‘1’, ’11’, ’11’, ‘testing’
    ) AS tmp
    WHERE NOT
    EXISTS (

    SELECT dis_date
    FROM dailydischarge
    WHERE dis_date = ‘2014-09-03’
    )
    LIMIT 1

  4. Hi Raw you code seems fine and should work. However I am writing it again, try to execute it once again as below but before that please make sure that your table “dailydischarge” has these fields only – dis_id, dis_date, station_id, emp_id, level, discharge, remarks

    Here is the code ;

    INSERT INTO dailydischarge (dis_date, station_id, emp_id, level, discharge, remarks)
    SELECT * FROM (SELECT ‘2014-09-03’, ‘1’, ‘1’, ’11’, ’11’, ‘testing’) AS tmp
    WHERE NOT EXISTS (
    SELECT dis_date FROM dailydischarge WHERE dis_date = ‘2014-09-03’
    ) LIMIT 1;

    Or the problem may be Probably because you are joining the tables and it is returning two columns with the same name with select * command.

    Please check again and update here.

    One more thing Raw, now we have moved to http://trickspocket.com and the same article can be found there as well http://trickspocket.com/mysql-insert-record-if-not-exists-in-table
    So it would be great if you comment there.

  5. Stopeck Raw says:

    first of am sorry for replying here once again, noticed your new domain the problem is that i am using a slow connection and now Disqus seems to be taking long time to load.

    I still get the same error, one thing i have noticed is that the new row contains station_id=’1′ & emp_id=’1′, which I assume is causing the duplication error of 1, or maybe is my mysql version am runing which is 5.0

    In this table one station(station_id) can have all month days from 1st-31st (dis_date)
    dis_date & station_id have many to many relationship if am right

    • Thanks once again Raw.
      No issues where you comment, the main things is that your problem should be solved and that is what the purpose of my blog is.

      What I suggest is first you try my above code in a separate table with your fields to check whether my codes are working or not. Then we will be able to figure out the actual problem. I think you are using join so first try the simple one than we should go for implenting it in the complex query.

      And please check that station_id field is not primary/unique key and also if possible can you send me the screenshot( here – nazir.2cool@gmail.com) of the error you are getting.

      SELECT ‘2014-09-03′, ‘1’, ‘1’, ’11’, ’11’, ‘testing’) – while copying, sometimes the quotes doesn’t show in proper format. Please have a look at that as well.

      wrong - SELECT ‘2014-09-03′, ‘1’, ‘1’, ’11’, ’11’, ‘testing’)

      Right - SELECT '2014-09-03', '1', '1', '11', '11', 'testing')

  6. contrid says:

    Actually, the solution to the “duplicate column name…” MySQL error is to assign/name each column in that subquery of INSERT.

    So in other words…

    SELECT * FROM (SELECT ‘Santosh’ AS name, ‘Kestopur’ AS address, ‘044’ AS tele) AS tmp

    I hope that helps

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow me on Twitter

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 442 other followers

%d bloggers like this: