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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
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?

Post 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.
Last edited by impulse() on Wed May 16, 2007 9:18 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Yes, it's possible to do with MySQL.
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post by impulse() »

Would it be something to do with assign primary keys over mutliple fields or is there a function to check this?
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post 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.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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).
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post by impulse() »

Do you know if the duplicate check over mutliple columns can be run in an insert statement?
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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:
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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)
)
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post by impulse() »

Just what the doctor ordered.

Thank you.
Post Reply