Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SIMPLE UPDATE - Just Drain Bead

Status
Not open for further replies.
JayKusch

JayKusch

MIS
Oct 30, 2001
3,199
0
0
US
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
 
Sort by date Sort by votes
TO SQLBILL: Error Message Reads ...


Server: Msg 4419, Level 16, State 1, Line 1
Derived table 'PIID' is not updatable because the definition contains the DISTINCT clause.
Thanks

J. Kusch
 
Upvote 0 Downvote
Last try before I go home for the day:

UPDATE Player_Information_Import_Data
SET Distinct_Player = 1
FROM (SELECT DISTINCT Full_Name, Address, City, State, Zip, Distinct_Player
FROM Player_Information_Import_Data) as PIID
WHERE PIID.Full_Name = Player_Information_Import_Data.Full_Name

-SQLBill
 
Upvote 0 Downvote
One more

UPDATE piid
SET Distinct_Player = 1
from Player_Information_Import_Data piid
where not exists (select * from Player_Information_Import_Data
where full_name = piid.full_name
and Address = piid.Address
and City = piid.city
and State = piid.state
and Zip = piid.Zip
group by Full_Name,Address,
City,State,Zip
having count(*) > 1)
 
Upvote 0 Downvote
Hi,

Try this

UPDATE Player_Information_Import_Data
SET Distinct_Player = 1
FROM
Player_Information_Import_Data
Inner Join
(Select DISTINCT Full_Name, Address, City, State,
Zip, Distinct_Player From Player_Information_Import_Data) TBL
on TBL.full_name = Player_Information_Import_Data.full_name
and TBL.Address = Player_Information_Import_Data.Address
and TBL.City = Player_Information_Import_Data.city
and TBL.State = Player_Information_Import_Data.state
and TBL.Zip = Player_Information_Import_Data.Zip

Sunil
 
Upvote 0 Downvote
Well Bill ... NO GO. Time to head to the house and clear the brain. Thanks for all the attempts. I am sure we are going to whip this puppy yet! lol. Take Care


NEXT !!!
Thanks

J. Kusch
 
Upvote 0 Downvote
I think swampBoogie is the closest so far with his last post. Using DISTINCT to get the subquery is not going to work because by definition there will be a row in that which matches every row in the table!

If swampBoogie's query doesn't work try this:

Code: 
UPDATE p
SET distinct_player = 1
FROM player_information_import_data p JOIN (
  SELECT full_name, address, city, state, zip
  FROM player_information_import_data
  GROUP BY full_name, address, city, state, zip
  HAVING COUNT(*) = 1
  ) dt ON p.full_name = dt.full_name
    AND p.address = dt.address
    AND p.city = dt.city
    AND p.state = dt.state
    AND p.zip = dt.zip

Anywhere near? --James
 
Upvote 0 Downvote
TO JamesLean ...

I agree w/ you. SwampBoogie seems to be the closest. I tried your w/ no effect. I have a group of 14 duplicate records I am using to review each attempt. Your query didn't toggle any of them to 1. I know there has to be an answer to this ... LOL.

Thanks ALL!!!
Thanks

J. Kusch
 
Upvote 0 Downvote
Does the subquery return the distinct records you want to update?:

Code: 
SELECT full_name, address, city, state, zip
FROM player_information_import_data
GROUP BY full_name, address, city, state, zip
HAVING COUNT(*) = 1
--James
 
Upvote 0 Downvote
TO: JamesLean ...

No, it is missing alot of records.
Thanks

J. Kusch
 
Upvote 0 Downvote
I can only assume then that you have problems with your actual data, or we've misunderstood what you are trying to do! Take some example rows:

<row> | full_name | address | city | state | zip
1 | J Lean | 1 A street | chicago | illinois | 123
2 | J Lean | 2 A street | chicago | illinois | 123
3 | J Lean | 2 A street | chicago | illinois | 123
4 | J Kusch | 1 A street | chicago | illinois | 123
5 | J Kusch | 1 A street | chicago | illinois | 123
6 | J Kusch | 3 A street | chicago | illinois | 123

The way I understand it, you want to mark rows 1 and 6 as they are the only unique rows. If so, then my subquery should return those rows.

If not, then can you clarify the problem?! --James
 
Upvote 0 Downvote
Try this.

UPDATE a SET Distinct_Player = 1
FROM Player_Information_Import_Data a
WHERE Exists
(SELECT Full_Name, Address, City, State, Zip
FROM Player_Information_Import_Data
WHERE Full_Name=a.FullName
AND Address=a.Address
AND City=a.City
AND State=a.State
AND Zip=a.Zip
GROUP BY Full_Name, Address, City, State, Zip
HAVING COUNT(*)=1) If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:
 
Upvote 0 Downvote
I got it!

SELECT beer
FROM refrigerator
WHERE label IN ('Bud', 'Coors', 'Miller', 'Samuel Adams', 'Heineken')
GROUP BY friends
HAVING a_big_party


Well, it will at least solve the &quot;Time to head to the house and clear the brain&quot; problem.
 
Upvote 0 Downvote
My money goes on tlbroadbent. That one looks like a winner!
After you fix the typo on line 6 of course. a.FullName should be a.Full_Name
 
Upvote 0 Downvote
In effect, Terry's is exactly the same as mine (and swampBoogie's really)! And like I said in my previous post, if it doesn't work then we may have misunderstood the requirement. --James
 
Upvote 0 Downvote
Morning ALL !!!

OK ... here are some facts.

The table Player_Information_Import_Data contains 467,241 records.

The 467K records are from over 70 sites.

I know for a fact that we have at the VERY LEAST 100K Distinct records if not closer to 300K.

The Queries from Terry, SwampBoogie and James are only updating 15,273 rows.

I am also monitoring a set of records that I know are duped. Here are the fields for the dupe recs:

'Domick Terrago','513 Impala Dr','Youngstown','Oh','44515'
'Domick Terrago','513 Impala Dr','Youngstown','Oh','44515'


The queries run so far have not set the Distinct_Player flag on either on of these records. I expected that one of these two records should have their flag set to 1 ... But no joy in Mudville ...

I sure appricate all the help and hope I can find a solution soon. Thanks all for all the great leads so far!!!
Thanks

J. Kusch
 
Upvote 0 Downvote
Ah-ha! I think we've found the confusion. So far we have been writing queries which find the unique rows, ie they have NO duplicates. I wouldn't expect either of the two rows you give in your example to get updated as they are not unique.

You want to mark one row from every set of duplicates as distinct (it doesn't matter which one). So in your example you would mark just one of those two rows.

Is this correct? I'm thinking of a solution for that now... --James
 
Upvote 0 Downvote
If I was correct, I think I've cracked it! Try this:

Code: 
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
--James
 
Upvote 0 Downvote
Ahhh... a new day and a fresh brain - LOL

A little more explaining here ...

All of these are approximations for example purpose:

1. Lets say out of 400K records that 200K are truely unique, NO DUPES, based on Full_Name, Address, City, State and Zip

--- I want all of the 200K worth of records to have their
Distinct_Player feild set to 1

2. Lets say the other 200K records ARE DUPES. So, lets make it simple ... Out of the 200K records we have here, we have 2 occurances of the feilds being identical (2 x 100,000 = 200,000).

--- I want to mark 1 record, between the two dupes, as being the representative &quot;Unique Record&quot;. Does not matter which record is set as long as 1 and only 1 is set.

SO ... after everything is said and done, I should have 300K records w/ the Distinct_Player field set to 1.

200,000 (Truely Unique records)
+ 100,000 (Single occurance from the Dupes Group)
---------
300,000 total records

Hope this clarifies things further ...

Thanks All ...
Thanks

J. Kusch
 
Upvote 0 Downvote
My last query should do that (at least it does on my test data!). Have you tried it? --James
 
Upvote 0 Downvote
Hi JayKusch,

If this is the case, I think update will not work as there is no way of distinguishing the set of records from one another and it will always update all similar records, so i think the option is to get disticnt records from the database and insert into a new table and drop the actaul table and rename the new table to the old one

so

SELECT DISTINCT Full_Name, Address, City, State, Zip, Distinct_Player Into NewTable
FROM Player_Information_Import_Data

drop Player_Information_Import_Data

exec sp_rename 'NewTable','Player_Information_Import_Data'

Sunil
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Brianfree
  • Locked
  • Question
Simple SQL Pivot
Replies
9
Views
104
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question
Update with a month name 1
Replies
9
Views
167
gmmastros
gmmastros
TheBugSlayer
  • Locked
  • Question
Transaction with SELECT and UPDATE with XLOCK causing deadlocks 2
Replies
10
Views
184
gmmastros
gmmastros
leifoet
  • Locked
  • Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
77
Chris Miller
Chris Miller
RazorEddie
  • Locked
  • Question
Simple query gone wrong
Replies
10
Views
75
RazorEddie
RazorEddie

Part and Inventory Search

Sponsor

Back
Top