Insert into table if not available

Asked

Viewed 5,798 times

3

How to insert data into a table only if it does not exist? the following attempt returns an error, the basic idea is to insert a mode record unique,if already existing ignore, otherwise enter the record.

INSERT INTO registro_tb (nome, tag)
VALUES ('Darwin', '99649')
WHERE NOT EXISTS (
    SELECT nome FROM registro_tb WHERE nome='value'
);

any suggestions?

mysql mariadb
  • 1

    Easy, when creating table structure, indicate that the field in question is unique this way if they try to create a record with this repeated field, the mySql will return an integrity breach attempt error

  • 2

    a specific column or all? depending on the case, replace into may be an option

  • It would be the combination of the line, it would work as a "Dictionary" .

  • http://stackoverflow.com/questions/690632/how-do-i-update-a-row-in-a-table-or-insert-it-if-it-doesnt-exist

1 answer

5


The simplest way I suggest is to add to the field name one CONSTRAINT single index. When trying to insert a new entry for the same name, Mysql will generate an error (which can be handled by caller insert).

To add the Constraint run the code:

ALTER TABLE registro_tb ADD CONSTRAINT nome UNIQUE

NOTE: There can be no duplicate records in the name field when performing this operation!

However, even with single active Constraint, it is possible to tell the MYSQL engine to ignore duplicate key error while trying to insert:

INSERT IGNORE INTO registro_tb (nome, tag)
VALUES ('Darwin', '99649')

This way it will be trivial to enter the record in the bank, without worrying if it already exists. This option is also more efficient and faster than trying to verify if the data exists before inserting and is indicated for scenarios with high data volume.

If you want to update the input if it already exists, you can use the following syntax:

INSERT IGNORE INTO registro_tb (nome, tag)
VALUES ('Darwin', '99649')
ON DUPLICATE KEY UPDATE tag=VALUES(tag)

In that case name needs to be a key Primary (as far as I know).

  • 1

    My old man, the easiest and least painful path: ALTER TABLE registro_tb
ADD CONSTRAINT nome UNIQUE

  • 1

    Detail that in the current database there can be no duplicate records, if any, will not work :)

  • @Marcelobonifazio I will add the code to add the Constraint to the answer, because it seems that the user who created the question may be little experienced with database. Nor had I thought to indicate how this should be done, because it is very obvious, and precisely not explain the obvious is that it leaves the beginners lost :-)

Browser other questions tagged mysql mariadb

You are not signed in. Login or sign up in order to post.