Find the syntax error - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Find the syntax error

Author

Topic

rohcky
Starting Member

38 Posts

Posted - 2005-05-10 : 11:44:48

Can anyone find the syntax error in this update statement:
------------------------------------------------------------------
UPDATE <table>
SET <int_var1> =
(SELECT SUM(b.<int_var2>)
FROM <table> AS b
WHERE a.<text_var> LIKE b.<text_var>)
FROM <table> a
------------------------------------------------------------------
lets say this is what the table looks like with records:

text int2 int1
-----------------------
a 1
a 3
a 4
a 7
b 3
b 3
b 3

The string is supposed to calculate the sum of each record that has matching text, so it will end up:

text int2 int1
-----------------------
a 1 15
a 3 15
a 4 15
a 7 15
b 3 9
b 3 9
b 3 9

Anyway, I get an error that says I can't compare text fields unless I use LIKE, which I am using. Am I missing something here?

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-05-10 : 14:31:27

try...

declare @deez table (test_text text, int2 int, int1 int)
insert into @deez
select 'a',1,null union all
select 'a',3,null union all
select 'a',4,null union all
select 'a',7,null union all
select 'b',3,null union all
select 'b',3,null union all
select 'b',3,null

select *
from @deez

update a
set int1 = (select sum(b.int2) from @deez b where cast(b.test_text as varchar) = cast(a.test_text as varchar))
from @deez a

select *
from @deez

- Nathan Skerl

mpetanovitch
Yak Posting Veteran

52 Posts

Posted - 2005-05-10 : 16:11:04

I would also make sure if its necessary to have a text column in your table. 99% of the time its not and should be converted to a varchar field.

Your query would work if you did that.

Mike Petanovitch

rohcky
Starting Member

38 Posts

Posted - 2005-05-11 : 07:56:35

mpetanovitch - I know that the query works with a numeric value. And the column does have to be a text field. That column is a unique identifier from another table, which is already populated and cannot be altered.

nathans - I tried your string, but I'm getting an error. Says 'syntax error near AS'. Doesn't seem to like the type cast.
Here is my actual string:

UPDATE Import_Usage_Dates_Day
SET LocUsageNoHours = (SELECT SUM(b.LocUsageNoHrs)
FROM Import_Usage_Dates_Day b
WHERE CAST (a.LocID AS nvarchar) = (b.LocID AS nvarchar)
FROM Import_Usage_Dates_Day a

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-05-11 : 12:12:11

examine this line:

WHERE CAST(a.LocID AS nvarchar) = CAST(b.LocID AS nvarchar))

working example:

set nocount on


declare @Import_Usage_Dates_Day table (LocID text, LocUsageNoHrs int, LocUsageNoHours int)

insert into @Import_Usage_Dates_Day
select 'a',1,null union all
select 'a',3,null union all
select 'a',4,null union all
select 'a',7,null union all
select 'b',3,null union all
select 'b',3,null union all
select 'b',3,null


select * from @Import_Usage_Dates_Day

UPDATE a
SET LocUsageNoHours = (SELECT SUM(b.LocUsageNoHrs) FROM @Import_Usage_Dates_Day b WHERE CAST(a.LocID AS nvarchar) = CAST(b.LocID AS nvarchar))
FROM @Import_Usage_Dates_Day a

select * from @Import_Usage_Dates_Day

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources