Need help flagging duplicate records.

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
LVL 2
brgdotnetcontractorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brgdotnetcontractorAuthor 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.
brgdotnetcontractorAuthor Commented:
Hi Anthony. I think I have it working based upon your query. I will test it out more today. Thank you.
Determine the Perfect Price for Your IT Services

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

Get Your eBook
Scott PletcherSenior DBACommented:
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
Anthony PerkinsCommented:
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 ...
brgdotnetcontractorAuthor Commented:
Thanks guys.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.