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

 

[SQLBill]

Your use of the SELECT in the WHERE is called a NESTED SELECT. It needs to be in parenthesis:

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

But that's not the main problem...a WHERE is a comparison and you don't compare anything in the WHERE clause (for example WHERE full_name = 'John Smith')

What exactly are you trying to do and can you provide an example?

-SQLBill

 

[jtmach]

Try it this way.

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

The hardest questions always have the easiest answers.

 

[JayKusch]

Ahhh my head hurts ... LOL

OK, trying to mark the field "Distinct_Player" to 1 for all distinct occurences of the record in the table

Thanks

J. Kusch

 

[JayKusch]

To JTMACH ... here is the error message I get.


Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '='.



As a simpler follow up: I am trying to gather ALL Distinct records in the table and set their "Distinct_Player" field to 1.

Thanks for the help so far !!!!
Thanks

J. Kusch

 

[jtmach]

Ok, this should work then.

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

The hardest questions always have the easiest answers.

 

[JayKusch]

To JTMACH ... NO GO ... Same Error Message


Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '='.



Thanks

J. Kusch

 

[SQLBill]

Try this:

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

I THINK that's what you need. I got the basic idea from the BOL, use the Index tab and enter UPDATE. Then open UPDATE (Described) and go to the bottom.

-SQLBill

 

[swampBoogie]

UPDATE Player_Information_Import_Data piid
SET Distinct_Player = 1
WHERE not exists (
SELECT 1
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 )

 

[JayKusch]

TO SQLBILL: Error Message Reads ...


Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.

Thanks

J. Kusch

 

[JayKusch]

TO SWAMPBOOGIE: Error Message Reads ...

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'piid'.

Thanks

J. Kusch

 

[SQLBill]

SLAP ON THE HEAD SOUND HERE! I left off the table name that's being updated:

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)


-SQLBill

 

[JayKusch]

To SQLBILL: Error Message Reads ...



Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near ')'.


I know I am slappin' my head over this one - LOL. Keep it comming folk ... we are bound to have a winner yet!
Thanks

J. Kusch

 

[SQLBill]

The BOL example I'm using is actually for updating based on TOP 10, and it uses an alias, so give this a try:

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

If that works I'll be pleasantly suprised.

-SQLBill

 

[JayKusch]

Well Bill ... That one actually modified the target field. BUT it set them ALL to 1. LOL.
Thanks

J. Kusch

 

[SQLBill]

Jay,

I suggest starting from 'square 1'. I'm thinking that the sub-SELECT isn't retrieving what you really want to update. So, pull the sub-SELECT out and run just it. Does it give you the results that you want updated? If not, then work on that select until you have it returning just the rows you need to update. Then put your new SELECT into the script.

I'm still wondering why the table alias was required since it wasn't used anywhere else.

-SQLBill

 

[JayKusch]

The Sub-Select is gathering the 88K records out of 132K. So that part is correct. I know want to mark those records by setting the Distinct_Player field to 1. MAN WE ARE CLOSE!!!
Thanks

J. Kusch

 

[swampBoogie]

New try

UPDATE piid
SET Distinct_Player = 1
from Player_Information_Import_Data piid inner join Player_Information_Import_Data piid2
on full_name = piid.full_name
and piid.Address = piid.Address
and piid.City = piid.city
and piid.State = piid.state
and piid.Zip = piid.Zip
group by piid.Full_Name,piid.Address,
piid.City,piid.State,piid.Zip
having count(*) = 1

 

[SQLBill]

What happens if you change the UPDATE as:

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


-SQLBill

 

[JayKusch]

TO SwampBoogie: Error Message Reads ...


Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'group'.


Getting Close ... lol
Thanks

J. Kusch