Problem with update query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Problem with update query

Author

Topic

gmcgahey
Starting Member

13 Posts

Posted - 2006-06-16 : 05:57:38

Hello
I have an update statement along the lines of below..

I need to read a value from the subquery (periodate) and use this to determine the field that is updated in the table (by using a case statement). I'm not sure if this is the correct way to do this. thanks

UPDATE table1 set (CASE
WHEN YEAR(PeriodDate) < year(getdate())
THEN lastyearbalance
WHEN YEAR(Date) = year(getdate()) THEN thisyearbalance
ELSE END) = sumbalance
FROM table1
LEFT
OUTER JOIN
(SELECT accountID, sumbalance, perioddate
FROM from table2)

as balsum ON table1.accountID = balsum.accountID

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-16 : 06:04:29

What's the need of Else here?
post some sample data...

gmcgahey
Starting Member

13 Posts

Posted - 2006-06-16 : 06:12:54

This is the sql I'm trying to get working, but it won't parse properly "Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'as'."

UPDATE gl set (CASE WHEN YEAR(per.edt) < year(getdate())
THEN gl.bal1 WHEN YEAR(per.edt) = year(getdate()) THEN gl.newbal1 END) = sumbal1
FROM
gl
LEFT
OUTER JOIN (
SELECT tj.glaccno, SUM(tj.bcamt) AS sumbal1, per.yr AS finyr, per.perid, per.perno, YEAR(per.edt) AS peryr
FROM tj INNER JOIN
per ON per.perid = tj.perid
GROUP BY tj.glaccno, per.yr, per.glclosed, per.perid, per.perno, YEAR(per.edt)
HAVING (per.glclosed = 0) AND (per.yr <= YEAR(GETDATE())) and (per.perno=1)
) as balsum ON gl.glaccno = balsum.glaccno

Thanks

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-16 : 06:29:21

UPDATE gl set sumbal1 = (CASE WHEN YEAR(per.edt) < year(getdate()) THEN gl.bal1
WHEN YEAR(per.edt) = year(getdate()) THEN gl.newbal1 END)
FROM
gl
LEFT
OUTER JOIN (
SELECT tj.glaccno, SUM(tj.bcamt) AS sumbal1, per.yr AS finyr, per.perid, per.perno, YEAR(per.edt) AS peryr
FROM tj INNER JOIN
per ON per.perid = tj.perid
GROUP BY tj.glaccno, per.yr, per.glclosed, per.perid, per.perno, YEAR(per.edt)
HAVING (per.glclosed = 0) AND (per.yr <= YEAR(GETDATE())) and (per.perno=1)
) as balsum ON gl.glaccno = balsum.glaccno

gmcgahey
Starting Member

13 Posts

Posted - 2006-06-16 : 06:46:25

THanks CSK but I'm trying to set the result of the case statement (field 'gl.bal1' or 'glnewbal1') equal to sumbal1 which is derived from the subquery

regards

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-16 : 06:56:21

is ur Fiirst Update Workes..?

gmcgahey
Starting Member

13 Posts

Posted - 2006-06-16 : 07:11:25

CSK
No I do not want to update the sumbal1 field, I need to update either 'gl.bal1' or 'glnewbal1' with sumbal1 from the subquery depending on the result of the case statement

thanks for ur hlp

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-16 : 08:51:03

you can use 2 update statement to do this


update gl
set    bal1 = sumbal1
from   gl left outer join ( . . . ) 
where  YEAR(per.edt) < year(getdate())

update gl
set    newbal1 = sumbal1
from   gl left outer join ( . . . ) 
where  YEAR(per.edt) = year(getdate())

gmcgahey
Starting Member

13 Posts

Posted - 2006-06-16 : 08:56:49

thank u khtan that does the job

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-16 : 09:00:14

if you really have to use one single update query

update gl
set    bal1    = case when YEAR(per.edt) < year(getdate()) then sumbal1 else bal1 end,
       newbal1 = case when YEAR(per.edt) = year(getdate()) then sumbal1 else newbal1 end
from   gl left outer join ( . . . )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources