How to I insert a table if it doesn’t exist in a MySQL database?

1152245 pts.
Tags:
Database
MySQL
MySQL Database
In a MySQL database, I'm trying to do the following query:
INSERT INTO table_listnames (name, address, tele)
VALUES ('Rupert', 'Somewhere', '022')
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name='value'
);
But it keeps returning the same error. I don't want to insert a record if the "name" field in the record already exists in another record. Is it possible to check if the name is unique. Thanks!
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Brijesh
    Hi,

    Try this:
    CREATE TABLE `table_listnames` (
      `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_listnames (name, address, tele)
    SELECT * FROM (SELECT 'Rupert', 'Somewhere', '022') AS tmp
    WHERE NOT EXISTS (
        SELECT name FROM table_listnames WHERE name = 'Rupert'
    ) LIMIT 1;
    
    Query OK, 1 row affected (0.00 sec)
    Records: 1  Duplicates: 0  Warnings: 0
    
    SELECT * FROM `table_listnames`;
    
    +----+--------+-----------+------+
    | id | name   | address   | tele |
    +----+--------+-----------+------+
    |  1 | Rupert | Somewhere | 022  |
    +----+--------+-----------+------+

    Try to insert the same record again:

    INSERT INTO table_listnames (name, address, tele)
    SELECT * FROM (SELECT 'Rupert', 'Somewhere', '022') AS tmp
    WHERE NOT EXISTS (
        SELECT name FROM table_listnames WHERE name = 'Rupert'
    ) LIMIT 1;
    
    Query OK, 0 rows affected (0.00 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    +----+--------+-----------+------+
    | id | name   | address   | tele |
    +----+--------+-----------+------+
    |  1 | Rupert | Somewhere | 022  |
    +----+--------+-----------+------+

    Insert a different record:

    INSERT INTO table_listnames (name, address, tele)
    SELECT * FROM (SELECT 'John', 'Doe', '022') AS tmp
    WHERE NOT EXISTS (
        SELECT name FROM table_listnames WHERE name = 'John'
    ) LIMIT 1;
    
    Query OK, 1 row affected (0.00 sec)
    Records: 1  Duplicates: 0  Warnings: 0
    
    SELECT * FROM `table_listnames`;
    
    +----+--------+-----------+------+
    | id | name   | address   | tele |
    +----+--------+-----------+------+
    |  1 | Rupert | Somewhere | 022  |
    |  2 | John   | Doe       | 022  |
    +----+--------+-----------+------+

    And so on...

    Hope this helps.

    14,450 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: