0

Possible Duplicate:
How do I update if exists, insert if not (aka upsert or merge) in MySQL?

I am creating a simple crawler gathering statistics from website. What I want to do is that if there is no record crawled from URL then insert to database, otherwise update the existing record, but I am not really sure how to do this without using php to

SELECT * from table where url='somevalue';

and if there is no record

INSERT INTO...

otherwise

UPDATE...
Community
  • 1
  • 1
Blackie123
  • 1,271
  • 4
  • 16
  • 22

1 Answers1

2

MySQL Manual :: INSERT INTO ... ON DUPLICATE KEY UPDATE Syntax

Dan Grossman
  • 51,866
  • 10
  • 112
  • 101
  • the problem is that url is not a unique key :/ – Blackie123 Jul 30 '11 at 02:50
  • 1
    Then make it one. There is no single query solution to this without such a constraint. – Dan Grossman Jul 30 '11 at 02:50
  • If i want to create a keyout of text column, it has to have a certain length which is not something I like (since the length can be pretty much random) not to mention that max length can be only 767 bytes – Blackie123 Jul 30 '11 at 02:53
  • 1
    Store a hash of the URL in another column and use that as the key. Fixed length and with a proper hashing algorithm, unlikely to ever collide on two different URLs. – Dan Grossman Jul 30 '11 at 03:03
  • Yea i did similar thing. the website my script crawls also stores their unique id in a URL so I just extracted it with simple regexp and used a simple bigint as a unique key. works perfectly! Thank you! – Blackie123 Jul 30 '11 at 15:21