SIMPLE UPDATE - Just Drain Bead

[JayKusch]

What am I missing ... trying to update a field in records that are distinct. SQL complaining that "Incorrect syntax near the keyword 'SELECT'." Here is the code ...


UPDATE Player_Information_Import_Data
SET Distinct_Player = 1
WHERE SELECT DISTINCT Full_Name,
Address,
City,
State,
Zip
FROM Player_Information_Import_Data

Thanks

J. Kusch

 

[JamesLean]

Sunil, did you see my query?! Try it out if you don't believe me:

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

--James

 

[sunila7]

Hi James,

ur query works .. if this is the data structure it shd work may be there is something more to Jaykusch's requirement... Otherwise ur query shd work

Sunil

 

[JayKusch]

Hey Folks ... I haven't bailed on this thread - LOL. I am running James's script right now. It has clocked 48 minutes and counting. The fields are indexed as a compound, So we should be seeing something out of the oven soon. I will absolutely leave a final comment once we get a winner. Thanks all!
Thanks

J. Kusch