another way to do this is using the INSERT IGNORE
statement. Assuming column1
should only hold unique values, you can add a UNIQUE KEY
constraint on the field (if its not your primary key already):
ALTER TABLE table ADD UNIQUE KEY column1 (column1)
you can then write your query as such:
INSERT IGNORE INTO table(column1, column2, column3) VALUES(@value1, @value2, @value3)
or if you want to automatically update the other fields, use ON DUPLICATE KEY UPDATE
:
INSERT INTO table(column1, column2, column3) VALUES(@value1, @value2, @value3)
ON DUPLICATE KEY UPDATE column2 = @value2, column3 = @value3
letting the database handle it automatically using unique key has several advantages:
- better performance and scalability through usage of indexes as opposed to full table scans
- failsafe - you can not add two rows with same unique identifier accidentally with a wrong query
- better readability of your queries
NOTE: a unique key may also span multiple rows. stupid example: combination of "ip" and "port" may be combined as the unique identifier "connection".