[SLV] MySQL check if 2 fields have been identical elsewhere?
Moderator: General Moderators
-
impulse()
- Forum Regular
- Posts: 748
- Joined: Wed Aug 09, 2006 8:36 am
- Location: Staffordshire, UK
- Contact:
[SLV] MySQL check if 2 fields have been identical elsewhere?
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.
Last edited by impulse() on Wed May 16, 2007 9:18 am, edited 1 time in total.
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
something like
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).
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;- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
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.
e.g.
Code: Select all
create table xyz (
foo int,
bar int,
primary key(foo, bar)
)