[RESOLVED] Inserting unique record MySQL

stonefish

Are you able to check if a record that you are about to insert into one table isnt already located in another table??

Obviously I can do a select query and check that way, however I am inserting multiple values in one statement and this is not efficient.

The column is called "URL" in both tables and they are both set to unique. Can I somehow check if the value I am inserting is unique to both tables??

tomhath

I'm not sure what you mean by "inserting multiple values in one statement".

What you want is roughly this (assumes you are on a version of MySQL that supports subselects and Unions):

insert into SomeTable (URL)
select 'someurl.com'
where not exists (
  select 1 from TheOtherTable where URL = 'someurl.com'
  UNION
  select 1 from SomeTable where URL = 'someurl.com'
)

stonefish

thanks for that,

however would this work when adding multiple values in one search query ie -

INSERT INTO list (url) VALUES ('http://www.yahoo.com','http:www.google.com','http://www.msn.com/')

is Mysql able to check all the values that are about to be inserted in the table??

tomhath

I've never seen an INSERT statement like that. It doesn't actually work, does it? 😕

INSERT INTO `list` -- table name is 'list'
(`url`)  -- implies you will supply a value for one column named 'url'
VALUES   

('http://www.yahoo.com',
 'http:www.google.com',
 'http://www.msn.com/') -- this implies you have three columns for one row, 
                       -- but you only named one column up above
                       -- this will not insert three rows

Sxooter

You guys to actually look at the manual sometimes, right?

replace

stonefish

Sorry i wrote that incorrectly, I meant -

however would this work when adding multiple values in one search query ie -

INSERT INTO list (url) VALUES ('http://www.yahoo.com'),('http:www.google.com'),('http://www.msn.com/')

is Mysql able to check all the values that are about to be inserted in the table??

tomhath

The normal way to do this is with triggers, but I've never used them in MySQL.

http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

Sxooter

EDIT: Oh, what table types are you operating on, cause on myisam the logic would be completely different than on innodb or bdb. I'm assuming you've got transactions, but with myisam you don't. Let me know and I'll clarify what I'm saying here to that type of table. You can make this work with either, btw... just differently and with different implications.

OK. The problem you're really fighting here is a race condition. Assume there's two transactions doing this at the same time. If you do it with a select on one table before you insert to another, and two transactions do that at once, on opposite tables, you can get a double entry.

This kind of says that maybe your table design is wrong. If this is a single column that both tables would share, but nothing else would be shared, then you'd be better off with one table with the URL referred to by the other two tables.

If the two tables are basically the same, but for being two different tables right now, then you make another field in the table design, merge the two, and use that to tell them apart.

If you absolutely can't do either, then you first insert into the "wrong" table. If the insert works, then there's no match. Then you insert into the correct table. If you can't insert, then something went wrong, because nothing should be inserted without putting something into the other table first right? Otherwise you insert into the other table, then go back and delete the dummy row in the other table as soon as you're done.

If you do this in a serializable transaction, then no other connections ever see tne "fake" row. If two things go to insert at the same time, one will wait for the other, since both can't insert, usually even in a transaction, against a unique key.

But I'd really redesign the tables into one with 2 or more entries for another field to differentiate them.

stonefish

Yes i am thinking merging the two tables into one is the simplest and fastest solution

Thanks!