SQL newbie looking for UPDATE help...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL newbie looking for UPDATE help...

Author

Topic

DoubleOZero
Starting Member

4 Posts

Posted - 2004-07-23 : 04:10:53

I'm attempting to create a database to help maintain a fantasy football league. I'm trying to write an UPDATE statement that updates a field to the result of a COUNT(*) function. I can't set the value to count(*) directly, and I'm not sure how to work around it. Here are the queries that I came up with.

This query generates the number of the CURRENT draft pick. This one works, is tested, and returns the correct result.

SELECT (Count(*)+1) AS DraftPick
FROM Players
WHERE Pick>0;

How do I use this result to update a number field (Players.Pick)in another query? I tried the following without much luck.

UPDATE Players
SET Players.FantasyTeam = 1,
Players.Pick = (SELECT (Count(*)+1) AS DraftPick
FROM Players
WHERE Pick>0)
WHERE Players.ID = 1;

I know there's something fundimentally wrong with trying to set a field equal to the result of the select query, but I don't know the correct way to do this. Any suggestions?

Thanks...

- 000

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-23 : 04:26:44

Maybe like this ?

UPDATE p
SET FantasyTeam = 1,
Pick = pp.Pick
FROM Players p,
(SELECT (Count(*)+1) AS DraftPick
FROM Players
WHERE Pick>0) as pp
WHERE p.ID = 1;

Duane.

DoubleOZero
Starting Member

4 Posts

Posted - 2004-07-23 : 04:59:27

Gave it a whirl, but Access (yep, I'm using Access :/) throws a Syntax Error at the second FROM command.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-23 : 05:25:07

oops! well fortunately I haven't had much exposure to Access

BTW - You should have put this in the Access Forum

Duane.

DoubleOZero
Starting Member

4 Posts

Posted - 2004-07-23 : 05:26:32

I tried this, but I recieved "Operation must include an updateable query."

UPDATE Players, [SELECT (Count(*)+1) AS result FROM Players WHERE Pick>0] AS p
SET Players.Pick = p.result
WHERE [Players].[PlayerID]=1;

Is my train of thought headed in the right direction?

-000

DoubleOZero
Starting Member

4 Posts

Posted - 2004-07-23 : 07:46:01

Okay, found the problem. Your solution was right, but Access's stripped down SQL doesn't allow FROM commands in UPDATE statements. The only workaround is to create a temporary table to store the data. Thats a lot of trouble to update one column. Sorry for wasting your time.

-000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources