Need help flagging duplicate records.

brgdotnet
brgdotnet used Ask the Experts™
on
Can someone help me with a query. I am lost as how to write this code.
I need to set the "Active" column to a value of Zero if it's DUP_PARAM value is equal to "2" and if there are duplicate values for the TransoporationValue within the same group of records with the same SSN.
Below is an example of what I am talking about. attached is the actual database table


Before Update :

SSN                       Active             DUP_PARAM       TransoporationValue

111119077            1                          2                      AirPlane
111119077            1                          0                      AirPlane
111119077            1                          3                      AirPlane
888990000              1                          3                          Train
222334000            1                          2                      Car
222334000            1                          13                    Car
222334000            1                          15                    Car
222334000            1                           2                      Truck

After Update :

SSN              Active             DUP_PARAM              TransoporationValue

111119077    0                             2                          AirPlane
111119077    1                             0                          AirPlane
111119077    1                             3                          AirPlane
888990000      1                             3                           Train
222334000    0                             2                           Car
222334000    1                             13                        Car
222334000    1                            15                         Car
222334000    1                             2                          Truck
Sample.txt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012
Commented:
I trust this is not going to be done on a large table, otherwise I would recommend re-designing.

UPDATE  t
SET     Active = 0
FROM    YourTable t
        INNER JOIN (SELECT  SSN,
                            TransportationValue
                    FROM    YourTable
                    GROUP BY TransportationValue
                    HAVING  COUNT(*) > 1
                   ) t2 ON t.SSN = t2.SSN
WHERE   t.DUP_PARAM = 2

Open in new window

brgdotnetcontractor

Author

Commented:
I tried your query, but I get an error message:


Msg 8120, Level 16, State 1, Line 6
Column 'STG.SSN' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Also, I have to live with the current table structure, I don't have a choice. Reasons I can't get into, so I will spare you the details.
brgdotnetcontractor

Author

Commented:
Hi Anthony. I think I have it working based upon your query. I will test it out more today. Thank you.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Be careful.  I think you also need to specify the Transportation value in the join.  And, for performance, you can limit the group'd results to only those that include a dup_param = 2 row.

UPDATE tn
SET Active = 0
FROM table_name tn
INNER JOIN (
    SELECT SSN, TransportationValue, COUNT(*) AS dup_count
    FROM table_name
    GROUP BY SSN, TransportationValue
    HAVING COUNT(*) > 1 AND SUM(CASE WHEN DUP_PARAM = 2 THEN 1 ELSE 0 END) > 0
) AS tn_dups ON
    tn_dups.SSN = tn.SSN AND
    tn_dups.TransportationValue = tn.TransportationValue

WHERE
    tn.DUP_PARAM = 2
Top Expert 2012

Commented:
I tried your query, but I get an error message:
Oops you are right it should be group by SSN, TransportationValue

But Scott may have a point ...
brgdotnetcontractor

Author

Commented:
Thanks guys.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial