Link to home
Start Free TrialLog in
Avatar of brgdotnet
brgdotnetFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of brgdotnet

ASKER

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.
Hi Anthony. I think I have it working based upon your query. I will test it out more today. Thank you.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 ...
Thanks guys.