2

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

  • Define the criteria for *checking to see if it exists*. – Salman A Mar 12 '12 at 10:24
  • 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 –  Mar 12 '12 at 10:27

3 Answers3

2

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.

juergen d
  • 201,996
  • 37
  • 293
  • 362
  • 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? –  Mar 12 '12 at 10:32
1

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

dotoree
  • 2,983
  • 1
  • 24
  • 29
0

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

TomDunning
  • 4,829
  • 1
  • 26
  • 33