September 1, 2009 at 11:39 am
Hi all. I use SQL Server 2005 and I can't figure out this SQL statement.
I have a table with 4 fields in it. The first three fields already have distinct values in them. There are 2299 records.
CREATE TABLE [dbo].[tblCSS2YRDB](
[DStmtNum] [int] NULL,
[CorpNum] [int] NULL,
[CVNum] [int] NULL,
[CVScore] [money] NULL
) ON [PRIMARY]
What I need to do is update the CVScore field with the AVG from a field in another table for each of the 2299 records. I was thinking this would be an outer query getting me the values of each of the fields for each record and then having a subquery that would update each of those records with the average. I just can't seem to figure it out.
Here is my latest try at it and of course it doesn't work:
SELECT DStmtNum, CVNum, CorpNum
FROM tblCSS2YRDB a
WHERE EXISTS(UPDATE tblCSS2YRDB SET CVScore = (SELECT Avg(Factor) as FactorAvg
FROM CSSAllRangeData
WHERE (DStmtNum = a.DStmtNum) AND (CVNum = a.CVNum) AND (CorpNum <> a.CorpNum))
I get this error when I try to execute this:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'UPDATE'.
I can code this to work in VB .NET in a For...NEXT loop with variables but it takes it like 30 minutes, basically the same as using a cursor in SQL.
Any help is graatly appreciated.
September 1, 2009 at 12:19 pm
This has now been resolved. I looked on another forum and picked up an idea and gave it a try and it worked. Here is the statement that worked:
UPDATE tblCSS2YRDB SET CVScore = (SELECT Avg(Factor) as FactorAvg
FROM CSSAllRangeData
WHERE (DStmtNum = a.DStmtNum) AND (CVNum = a.CVNum) AND (CorpNum a.CorpNum))
FROM tblCSS2YRDB a
WHERE EXISTS (SELECT DStmtNum, CVNum, CorpNum
FROM tblCSS2YRDB)
September 1, 2009 at 1:07 pm
Other than slowing down your query a little - your WHERE clause does nothing for you. You're checking to see if there are rows in the table you also happen to be updating. you can't update rows that aren't in the table, so that EXISTS adds no value (other than making the query more complicated)
what were you trying to get it to do for you?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 1, 2009 at 11:15 pm
Matt has a point. You don't need the outer where clause at all. Also, when you alias a table in the from clause, you can use that alias in the update portion.
This might be a slightly easier to read version of your command:
UPDATE A
SET CVScore = (SELECT Avg(Factor) as FactorAvg
FROM CSSAllRangeData
WHERE (DStmtNum = a.DStmtNum) AND (CVNum = a.CVNum) AND (CorpNum a.CorpNum))
FROM tblCSS2YRDB a
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply