CODEWITHSUNDEEP
mysql
Domi.Zhang
Domi.Zhang

Reputation: 37

Cannot insert row when table is empty in MySql

I wanna insert a row when the name not exists in the table.

When the table is empty,it cannot insert anything, anyone can help me?

Here is my code:

INSERT INTO `ediftpdb`.`users`(
      name
      ,passwd
      ,vendor
    )
    SELECT
      'L001'
      ,'12345678a'
      ,'MKTPLS'
    FROM `ediftpdb`.`users`
    WHERE NOT EXISTS (SELECT * FROM `ediftpdb`.`users` WHERE name='L001' AND vendor = 'MKTPLS' ) LIMIT 1;

P.S. I found a funny stuff, when ediftpdb.users is empty, code like below returns nothing.

SELECT
  'L001'
  ,'12345678a'
  ,'MKTPLS'
FROM `ediftpdb`.`users`

Upvotes: 0

Views: 573

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

As @Martin Smith pointed, when the table ediftpdb.users is empty the FROM ediftpdb.users results in no rows. If it had 100 rows, then your statement would try to INSERT 100 (identical) records into the table.

Try this:

INSERT INTO
     ...
    SELECT
      'L001'
      ,'12345678a'
      ,'MKTPLS'
    FROM (SELECT 1) AS dummy
    WHERE NOT EXISTS ...

Upvotes: 2

nobody
nobody

Reputation: 10645

The better way to do this is to create a unique multi-part index on name and vendor columns:

CREATE UNIQUE INDEX name_vendor ON ediftpdb.users( name, vendor )

Then:

INSERT IGNORE INTO ediftpdb.users ( name, passwd, vendor )
  VALUES ( 'L001', '12345678a', 'MKTPLS' )

will do exactly what you want to do.

Upvotes: 2

Related Questions