Adding to row in MySQL if it doesn't exist

Question

When adding a row to a table, but first checking to see if it exists first Which would be the most efficient way of handling this?

Would it be a case of query to see if it exist, if not then insert.

Or using on duplicate?

Or simply replace (Would this work, if the row did not exist)?

Thanks

possible duplicate of [How to 'insert if not exists' in MySQL?](http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql) — John Woo, Mar 12 '12 at 10:26
Maybe, I didnt explain very well sorry. I was kind of meaning how best to go about it would checking to see it exists first be the best route — Rory Standley, Mar 12 '12 at 10:27

juergen d · Accepted Answer · 2012-03-12T10:31:13.853

3

I think this is the fastest way in MySQL:

REPLACE into table (col1, col2) values(1, 'ABC')

EDIT:

MySQL will delete the row if it does exist and insert a new one.

edited Mar 12 '12 at 10:31

answered Mar 12 '12 at 10:24

juergen d

186,950
30
261
325

Yes. It would insert a new row then. – juergen d Mar 12 '12 at 10:26
This will insert the record anyway REPLACE = DELETE + INSERT – dotoree Mar 12 '12 at 10:27
no a delete but an UPDATE, which means also that it will not do update if the fields are equals – Kharaone Mar 12 '12 at 10:29
1

Would this query still work with PHP mysql_insert_id? If the row existed, obviously it would not insert, would I still be abel to pull the id from the existing row without having to run another query? – Rory Standley Mar 12 '12 at 10:32

score 2 · Answer 2 · answered Mar 12 '12 at 10:25

2

I think you need INSERT IGNORE see this or INSERT ON DUPLICATE KEY UPDATE

answered Mar 12 '12 at 10:25

dotoree

2,935
1
21
28

score 1 · Answer 3 · answered Mar 12 '12 at 10:27

depends what you mean by 'exists' if there is a unique field you can check against such as 'email' or 'login' then you can just try the insert and mysql raise error if exists, otherwise you'd do the replace as juergen d just suggested.

NB: don't use replace if you wanted your timestamps for any reason (such as if they're used in encrypted passwords or salt

Adding to row in MySQL if it doesn't exist

3 Answers3

Linked