3. Fruit Code and Fruit Variety Code – Case 1

This is a continuation of the post called SQL Server Data Validation Introduction.

This is the original Fruits table. We can use this as an example of a much larger table with the same columns but thousands of rows.

Consider the following table that has a duplicate in the Fruit Variety. We need to fix this problem with the data. First we need to write a query that will identify ALL the rows that have a problem like this. Our table could have thousands of rows in it. Here we are just showing a small sample of data.

The following SQL code will show all duplicates of FruitCode and FruitVarietyCode.

  select FruitCode, FruitVarietyCode, Count(*) as Num
  from [FruitsErrorDupVariety]
  group by FruitCode, FruitVarietyCode
  having Count(*) > 1

Here are the results.