Page 1 of 1

[SLV] MySQL check if 2 fields have been identical elsewhere?

Posted: Tue May 08, 2007 10:21 am
by impulse()
I was hoping I could get MySQL to check if 2 columns of data have been identical on a different row. I can do this with PHP but it just seems one of those things that MySQL could handle.

Posted: Tue May 08, 2007 10:22 am
by feyd
Yes, it's possible to do with MySQL.

Posted: Tue May 08, 2007 10:24 am
by impulse()
Would it be something to do with assign primary keys over mutliple fields or is there a function to check this?

Posted: Wed May 16, 2007 4:20 am
by impulse()
Could you show me what I should be looking for to learn about this. I worked around it with PHP because but now I need to do same sort of thing and it wouldn't be practical to use PHP.

Posted: Wed May 16, 2007 4:34 am
by CoderGoblin
something like

Code: Select all

SELECT taba.id as id_one,tabb.id AS id_two ,col1 AS duplicate 
FROM tablename AS taba, tablename AS tabb 
WHERE taba.col1=tabb.col2 AND taba.col1<>taba.col2;
OK untested but principle is that you use the tablename twice calling them differently. (I use Postgres not MySQL dont't know if that makes a difference).

Posted: Wed May 16, 2007 4:48 am
by impulse()
Do you know if the duplicate check over mutliple columns can be run in an insert statement?

Posted: Wed May 16, 2007 5:04 am
by CoderGoblin
Without knowing exactly what you are trying to do and potentially testing it I have no idea, especially with MySQL. First thing to ask is if the above statement (changed to be relevant to your code) actually works.
:wink:

Posted: Wed May 16, 2007 8:05 am
by volka
Yes, you can define an unique index for more than one field. Mysql will not insert a second record if another record with the same values in those fields already exists.

e.g.

Code: Select all

create table xyz (
  foo int,
  bar int,
  primary key(foo, bar)
)

Posted: Wed May 16, 2007 8:27 am
by impulse()
Just what the doctor ordered.

Thank you.