Mysql detect duplicate value in many column

Question

I wanna ask about duplicate value detection on mysql table with so many column in it. my table name is inventory that have person_name , 40 column for itemid , and 40 column for item serial number like this : person_name, itemid0, itemid1, itemid2......itemid39 AND for serial number itemserial0, itemserial1, itemserial2....itemserial39

so what i want to.. find example : i have serial number like '888239390707418232' Find 888239390707418238 in every column itemserial itemserial0, itemserial1 ... itemserial39

if this value found on itemserial23 so the query will make output

person_name | itemid23 | itemserial23       |
John        | 1234     | 888239390707418232 |
Bryan       | 1234     | 888239390707418232 |

The itemID are able to duplicate , but itemserial is not able

Table Structure (if you confused about my table):
person_name, it0, is0, it1, is1, it2, is2, it3, is3 ..... until it39, is39

I just try to reveal that result with this query :

SELECT person_name, 
it0, is0, it1, is1, it2, is2, it3, is3, 
it4, is4, 
it5, is5, 
it6, is6, 
it7, is7, 
it8, is8, 
it9, is9, 
it10, is10, 
it11, is11, 
it12, is12, 
it13, is13, 
it14, is14, 
it15, is15, 
it16, is16, 
it17, is17, 
it18, is18, 
it19, is19, 
it20, is20, 
it21, is21, 
it22, is22, 
it23, is23, 
it24, is24, 
it25, is25, 
it26, is26, 
it27, is27, 
it28, is28, 
it29, is29, 
it30, is30, 
it31, is31, 
it32, is32, 
it33, is33, 
it34, is34, 
it35, is35, 
it36, is36, 
it37, is37, 
it38, is38, 
it39, is39 
FROM gdb0101.inventory
WHERE is1 = '888239390707418232' OR is2 = '888239390707418232'
OR is3= '888239390707418232'
OR is4= '888239390707418232'
OR is5= '888239390707418232'
OR is6= '888239390707418232'
OR is7= '888239390707418232'
OR is8= '888239390707418232'
OR is9= '888239390707418232'
OR is10= '888239390707418232'
OR is11= '888239390707418232'
OR is12= '888239390707418232'
OR is13= '888239390707418232'
OR is14= '888239390707418232'
OR is15= '888239390707418232'
OR is16= '888239390707418232'
OR is17= '888239390707418232'
OR is18= '888239390707418232'
OR is19= '888239390707418232'
OR is20= '888239390707418232'
OR is21= '888239390707418232'
OR is22= '888239390707418232'
OR is23= '888239390707418232'
OR is24= '888239390707418232'
OR is25= '888239390707418232'
OR is26= '888239390707418232'
OR is27= '888239390707418232'
OR is28= '888239390707418232'
OR is29= '888239390707418232'
OR is30= '888239390707418232'
OR is31= '888239390707418232'
OR is32= '888239390707418232'
OR is33= '888239390707418232'
OR is34= '888239390707418232'
OR is35= '888239390707418232'
OR is36= '888239390707418232'
OR is37= '888239390707418232'
OR is38= '888239390707418232'
OR is39= '888239390707418232'

but the result is so confusing it = mean itemid is = mean itemserial

This query will be use by PHP application to show on table

Any suggestion or help will be appreciated :)

I don't understand the question. Is your query working? If not, what is wrong with it? What data do you want? Are you just looking for a particular serial number in any of the 40 columns? What is it0 and is0? Are they related to each other? — Gordon Linoff
– Gordon Linoff, Commented Oct 5, 2015 at 19:31
hello , what i need is , in my php script there are input text to search item serial like example 123456 , and i need to search item serial (is0, is1, is2, .... is39) where 'is0...is39' matching with 123456. FYI it0 mean itemID index 0 , is0 mean itemserial index 0. — Bayu Anggara
– Bayu Anggara, Commented Oct 6, 2015 at 7:01

urfusion · Accepted Answer · 2015-10-05 20:22:30Z

1

Try this

SELECT distinct inventory.*
  FROM inventory as tb1
  join inventory as tb2
 WHERE tb1.itemid23 = tb2.itemid23 
   and tb1.id < tb2.id

it might help you to fetch non duplicate records

for deleting duplicate records

DELETE inventory
  FROM inventory as tb1
  join inventory as tb2
 WHERE tb1.itemid23 = tb2.itemid23
   and tb1.id < tb2.id

edited Oct 5, 2015 at 20:22

answered Oct 5, 2015 at 19:12

urfusion

5,4995 gold badges56 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Bayu Anggara Over a year ago

Hi Thanks for answering this question ! i just confused , you mean FROM person_name (actually my table name is inventory) , JOIN person_name ? , person_name is not a table , that are column name. can you read my question again ? , maybe you miss :) , Thank you

Bayu Anggara Over a year ago

the query is working , but it take like forever :( , my table has 80k row , can you give me any suggestion for optimization ? and how i can check serial number to whole column ? column itemserial on table inventory has 39 column per row , :(

urfusion Over a year ago

I think you should remove duplicate data to reduce the data

Bayu Anggara Over a year ago

Yeah , i will , but i wanna detect the duplicate data first, this is game table , so i want to track if item serial is duplicated , because if one or two or more item has same serial , it mean the item has been cheated :) if you know what i mean

Bayu Anggara Over a year ago

Sorry but i wanna ask first , why JOIN same table with different alias ? this is to compare same table ? can you help me make one table query ? and how about other item serial ? eg : itemserial1 , itemserial2 until itemserial39 and for your information , this table didn't have a id on primary key , but person_name for primary key :(

Collectives™ on Stack Overflow

Mysql detect duplicate value in many column

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related