brgdotnet
asked on
How to set a column value based upon duplicate records
Suppose I have the following data in a table named "Teams"
If I have a table of football teams.
I want to update the table and set the "Flag" column to 1, if there are any duplicate football teams in the table.
The TeamId will determine if a row is a duplicate, since the TeamId would occur more than once. Here is an example of the table before the update.
TeamId TeamName Flag
BAL Baltimore Ravens
BAL Baltimore Ravens
BAL Baltimore Ravens
PAT NE Patriots
SF9 SF 49ers
SF9 SF 49ers
SF9 SF 49ers
SDC San Diego Chargers
Here is how the table would look after the update. All duplicate rows have their Flag set to a value of "1"
TeamId TeamName Flag
BAL Baltimore Ravens 1
BAL Baltimore Ravens 1
BAL Baltimore Ravens 1
PAT NE Patriots
SF9 SF 49ers 1
SF9 SF 49ers 1
SF9 SF 49ers 1
SDC San Diego Chargers
INSERT INTO Team (ID,FirstName,LastName) VALUES ('BAL', 'Ravens',null)
INSERT INTO Team (ID,FirstName,LastName) VALUES ('BAL', 'Ravens',null)
INSERT INTO Team (ID,FirstName,LastName) VALUES ('BAL', 'Ravens',null)
INSERT INTO Team (ID,FirstName,LastName) VALUES ('PAT', 'Patriots',null)
INSERT INTO Team (ID,FirstName,LastName) VALUES ('SF9', '9ers',null)
INSERT INTO Team (ID,FirstName,LastName) VALUES ('SF9', '9ers',null)
INSERT INTO Team (ID,FirstName,LastName) VALUES ('SF9', '9ers',null)
INSERT INTO Team (ID,FirstName,LastName) VALUES ('SF9', '9ers',null)
If I have a table of football teams.
I want to update the table and set the "Flag" column to 1, if there are any duplicate football teams in the table.
The TeamId will determine if a row is a duplicate, since the TeamId would occur more than once. Here is an example of the table before the update.
TeamId TeamName Flag
BAL Baltimore Ravens
BAL Baltimore Ravens
BAL Baltimore Ravens
PAT NE Patriots
SF9 SF 49ers
SF9 SF 49ers
SF9 SF 49ers
SDC San Diego Chargers
Here is how the table would look after the update. All duplicate rows have their Flag set to a value of "1"
TeamId TeamName Flag
BAL Baltimore Ravens 1
BAL Baltimore Ravens 1
BAL Baltimore Ravens 1
PAT NE Patriots
SF9 SF 49ers 1
SF9 SF 49ers 1
SF9 SF 49ers 1
SDC San Diego Chargers
INSERT INTO Team (ID,FirstName,LastName) VALUES ('BAL', 'Ravens',null)
INSERT INTO Team (ID,FirstName,LastName) VALUES ('BAL', 'Ravens',null)
INSERT INTO Team (ID,FirstName,LastName) VALUES ('BAL', 'Ravens',null)
INSERT INTO Team (ID,FirstName,LastName) VALUES ('PAT', 'Patriots',null)
INSERT INTO Team (ID,FirstName,LastName) VALUES ('SF9', '9ers',null)
INSERT INTO Team (ID,FirstName,LastName) VALUES ('SF9', '9ers',null)
INSERT INTO Team (ID,FirstName,LastName) VALUES ('SF9', '9ers',null)
INSERT INTO Team (ID,FirstName,LastName) VALUES ('SF9', '9ers',null)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you
ASKER
update ClubMembers
set ClubMembers.IsDup = 1 -- (or 1 if it's a number as opposed to a char)
where ClubMembers.ID in
(
select ID from ClubMembers group by ID having count(*) > 1
)