Find duplicates with a where clause

0
votes

mysql table structure meta_id, post_id, meta_key, meta_value (WordPress posmeta table)

I want to find all duplicated rows in the meta_value table - but only where meta_key = _customer_user

I know how to find duplicates easily enough - but I can't figure out a way to filter them for just meta_key = _customer_user

Any ideas?

2
mysql
show some sample data. it will easy to give ans - krishn Patel
hey @knowledge.... sure thing - this is what the table looks like i.imgur.com/PeSVkrQ.png - Ed Bloom
what is the duplicate here ? - krishn Patel
@knowledge.... that screenshot shows the dataset already filtered with a simple WHERE meta_key = '_customer_user' clause. There are other values in the meta_key table. I'm only interested in rows where meta_key = _customer_user - Ed Bloom
@knowledge.... the duplicate is the meta_value value - so in the dataset in the screenshot, the value "2" is the customer - you can see there are 5 rows with the meta_value "2" - Ed Bloom

2 Answers

0
votes

If I understood your question you can try;

    SELECT  meta_key, meta_value 
    FROM 
    ( SELECT meta_key, meta_value, COUNT(*) AS RC
      FROM YOUR_TABLE
      WHERE meta_key='_customer_user'
      GROUP BY meta_key, meta_value ) A 
    WHERE RC>0

Or in a shorter form:

  SELECT meta_key, meta_value 
  FROM YOUR_TABLE
  WHERE meta_key='_customer_user'
  GROUP BY  meta_key, meta_value 
  HAVING COUNT(*) >0
0
votes
    select group_concat(meta_id) from TableName where meta_key ='_customer_user'
        having meta_id <> min(meta_id)
group by meta_value

it will give you all meta id where is duplicate. it will not include first insert id.