syntax for updating sql.. err - 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

syntax for updating sql.. err

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2010-04-14 : 09:25:44

hi

i need to update with single query like below

UPDATE A
SET CODE = C.Code
FROM [dbo].[TESTA] A
INNER JOIN [dbo].[TESTB] B
ON A.ID = B.ID
INNER JOIN [dbo].[TESTC] AS C
ON B.KEYNM = C.KEYNM
WHERE A.NAME = 'R'
GROUP BY C.KEYNM
HAVING C.KEYNM In (60,70)

but getting error
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'GROUP'.

T.I.A

need help

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-14 : 09:31:38

Why are you using GROUP BY and HAVING? Does this not do what you want?

UPDATE A
SET CODE = C.Code
FROM [dbo].[TESTA] A
INNER JOIN [dbo].[TESTB] B
ON A.ID = B.ID
INNER JOIN [dbo].[TESTC] AS C
ON B.KEYNM = C.KEYNM
WHERE A.NAME = 'R'
AND C.KEYNM In (60,70)

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2010-04-14 : 09:44:03

thanks for ur reply

but actually i need that group by clause as in that table most of values are duplicate having different values in other columns..

T.I.A

Do needful

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-04-14 : 09:55:22

how about the following type of structure?

update a set a.col1 = d.cd
from (select b.col1 as ab, sum(c.x) as cd from b inner join c on b.col2 = c.col2 where b.y = "z" group by b.col1) d

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-14 : 09:59:56

Perhaps you could explain in detail exactly what it is you're trying to achieve.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources