CREATE TABLE player_information_import_data (
full_name varchar(20),
address varchar(20),
city varchar(20),
state varchar(20),
zip varchar(20),
distinct_player bit
)
INSERT player_information_import_data VALUES ('James Lean','1 A street','chicago','illinois','123',0)
INSERT player_information_import_data VALUES ('James Lean','1 A street','chicago','illinois','123',0)
INSERT player_information_import_data VALUES ('James Lean','1 A street','chicago','illinois','123',0)
INSERT player_information_import_data VALUES ('James Lean','2 A street','chicago','illinois','123',0)
INSERT player_information_import_data VALUES ('Jay Kusch','2 A street','chicago','illinois','123',0)
INSERT player_information_import_data VALUES ('Jay Kusch','2 A street','chicago','illinois','123',0)
INSERT player_information_import_data VALUES ('Jay Kusch','3 A street','chicago','illinois','123',0)
SELECT * FROM player_information_import_data
SET ROWCOUNT 1
DECLARE @rc int
SET @rc = 1
WHILE @rc = 1
BEGIN
UPDATE p1
SET distinct_player = 1
FROM player_information_import_data p1
WHERE NOT EXISTS (
SELECT full_name, address, city, state, zip
FROM player_information_import_data
WHERE full_name = p1.full_name
AND address = p1.address
AND city = p1.city
AND state = p1.state
AND zip = p1.zip
AND distinct_player = 1
)
SET @rc = @@ROWCOUNT
END
SET ROWCOUNT 0
SELECT * FROM player_information_import_data
DROP TABLE player_information_import_data