Syntax Error IS NULL

Question

Syntax Error IS NULL

Welsh Corgi

SSC Guru

Points: 116520
More actions
July 13, 2015 at 8:03 am

#300065

What is wrong with the following Statement?
update #tmpTotals set StateCount = (Select ISNULL Sum(DayCount),0) from #tmpTotals t where Loss_state = #tmpTotals.Loss_State )
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

Welsh Corgi (7/13/2015)
What is wrong with the following Statement?
update #tmpTotals set StateCount = (Select ISNULL Sum(DayCount),0) from #tmpTotals t where Loss_state = #tmpTotals.Loss_State )

You're missing the "(" after ISNULL

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

I execute this and I get the following:

update #tmpTotals set StateCount = ISNULL(Select Sum(DayCount,0) from #tmpTotals t where Loss_state = #tmpTotals.Loss_State )

Msg 156, Level 15, State 1, Line 40

Incorrect syntax near the keyword 'Select'.

Msg 174, Level 15, State 1, Line 40

The Sum function requires 1 argument(s).

Please do not put inside a SQL Code tag so I can copy and paste.

Thank you very much.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

As the eror states, SUM requires one parameter, you've specified two.

Sum(DayCount,0)

You need to move the ,0 to the end and add another bracket.

p.s. you can copy from inside a code tag

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

I'm not getting the syntax right.:blush:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Try first writing it as a select with a FROM and a derived table in the FROM clause (not a subquery in the SELECT). Once you have that correct it's pretty easy to turn into an update

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

??

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Write a select which gets the results you want. Write it so that it will return the values you want the updated table to have. Then, once you have that, convert it to an update.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

drew.allen SSC Guru Points: 76985 More actions · Answer 8

ISNULL() requires an opening paren and the subquery also requires an opening paren. You are trying to conflate them. You also have the second argument of the ISNULL() in the middle of the subquery.

update #tmpTotals SET StateCount = ISNULL((SELECT SUM(DayCount) FROM #tmpTotals t WHERE Loss_state = #tmpTotals.Loss_State ), 0)

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 9

edit>>>

why not provide simple create table / insert data scripts that explains your issue......this can be sorted very quickly I am sure

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

drew.allen (7/13/2015)
ISNULL() requires an opening paren and the subquery also requires an opening paren. You are trying to conflate them. You also have the second argument of the ISNULL() in the middle of the subquery.
update #tmpTotals SET StateCount = ISNULL((SELECT SUM(DayCount) FROM #tmpTotals t WHERE Loss_state = #tmpTotals.Loss_State ), 0)
Drew

Hi Drew,

I can't copy sand paste the code.:blush:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 11

I can't copy sand paste the code.:blush:

browser issue?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

I can copy and paste but not the whole line when I try and scroll.

Could you please place the code outside of the CQL Code Tag?

Thanks.:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 13

Welsh Corgi (7/13/2015)
I can copy and paste but not the whole line when I try and scroll.
Could you please place the code outside of the CQL Code Tag?
Thanks.:-)

Drews code for you...without any code tags

update #tmpTotals SET StateCount = ISNULL((SELECT SUM(DayCount) FROM #tmpTotals t WHERE Loss_state = #tmpTotals.Loss_State ), 0)

would probably have been quicker for you to just type into SSMS....but heyho...hope this helps

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

drew.allen SSC Guru Points: 76985 More actions · Answer 14

Welsh Corgi (7/13/2015)
I can copy and paste but not the whole line when I try and scroll.
Could you please place the code outside of the CQL Code Tag?
Thanks.:-)

Also, when you quoted my post to reply to it, the code was right there for you to copy and paste.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA