Select rows that are different in SQL

Question

I have a table with way too many columns and a couple million rows that I need to query for differences.

On these rows there will hopefully be only one column that is different and that should be the Auto incremented id field.

What I need to do is check to see if these rows ARE actually the same and if there are any that have any differences in any of the fields.

So for example, if the "Name" column is supposed to be "Peter, Paul and Mary" and the "Order #" column is supposed to be "132" I need to find any rows where those values aren't true, but I need to find it for every column in the table AND I don't actually know what the correct values are (meaning I can't just create a "SELECT...WHERE Name='This'" for each column).

So how can I find the rows that are different? (using straight SQL, no programming)

Answer 1

If you know the limit of the wrong results (say 10 for example) then you could order them and get only the first 11 results. You see where I am going with this, right?

I have no SQL expertise whatsoever though :)

Answer 2

Do you need to do this programmatically, or can you just run a few queries yourself to check it?

If the latter, I'd just do "select distinct name, order#" to start. This should return a list that includes "Peter Paul and Mary, 132" and possibly some other things.

Then find the other things by doing select ... where name = "this" as you suggest.

You could get even more info out of that first query by doing "select distinct name, order#, count(*) from ... group by name, order#". This would give you both the list of values and the frequency of a given set of values.

Answer 3

if I understand you correctly, (your question is not 100% clear to me), you are tryin g to find the rows that are unnecessary duplicates ? If so, Try these SQL queries:

Select A.Id, B.Id
From Table A
   Join Table B       
     On A.Id <> B.Id
       And A.ColA = B.ColA
       And A.ColB = B.Col
       And A.ColC = B.ColC
        ...

Or

 Select ColA, ColB, etc.
 From Table
 Group By ColA, ColB, etc.
 Having Count(*) > 1

Answer 4

Would you think this answer is what you are looking for and would help you? here's a Link to find the appropriate sql query.

Answer 5

If you have a correlation between two "independent" columns where there is really only one "correct" value for column B whenever column A is a given value, then you have a broken database design, because these correlation should have been factored out as a separate table.

Answer 6

Try this:

SELECT Name, OrderNum
FROM Orders T1
FULL OUTER JOIN (
    SELECT Name, OrderNum
    FROM Orders
    GROUP BY Name, OrderNum
    HAVING COUNT(*) > 1) T2
    ON T1.Name = T2.Name
        AND T1.OrderNum = T2.OrderNum

The nested select is identifying the duplicates, so you will need to target your common fields, the FULL OUTER JOIN excludes the duplicates from your result set. So essentially you are joining the table on itself to identify the duplicates and exclude them from your results. If you want only the duplicates then change the FULL OUTER JOIN to just JOIN.