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 unique mode record, if it already exists ignore, otherwise insert the record.
INSERT INTO registro_tb (nome, tag)
VALUES ('Darwin', '99649')
WHERE NOT EXISTS (
SELECT nome FROM registro_tb WHERE nome='value'
);
Any suggestions?
The simplest way I indicate is to add a CONSTRAINT single index to the name field. When attempting to insert a new entry for the same name, MySQL will generate an error (which can be handled by the caller of the INSERT).
To add the constraint execute the code:
ALTER TABLE registro_tb ADD CONSTRAINT nome UNIQUE
NOTE:
There can not be duplicate records in the name field when performing this operation!However, even with the single constraint active, you can tell the MYSQL engine to ignore the duplicate key error when attempting to insert:
INSERT IGNORE INTO registro_tb (nome, tag)
VALUES ('Darwin', '99649')
In this way it will be trivial to insert the record into the bank, without worrying if it already exists. This option is also more efficient and faster than trying to verify that the data exists before insertion and is indicated for scenarios with high data volume.
If you want to update the entry 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 this case name must be a primary key (as far as I know).