data not staying in table - 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

data not staying in table

Author

Topic

mgossman
Starting Member

3 Posts

Posted - 2008-07-25 : 19:53:03

I have a table that I'm trying to build. It has a description and ytd totals. I build the table and load the descriptions, but when I try to calculate in the YTd totals, only the last one loaded stays in the table. Here's my code:

SQL: insert into {table5} (Activity) select 'Tree Maintenance';
SQL: insert into {table5} (Activity) select 'Landscape Maintenance';
SQL: insert into {table5} (Activity) select '# Stand-By Requests';
SQL: insert into {table5} (Activity) select '# Facilities Maintenance Requests';

SQL: update {table5} set total_this_yr = (select count(issue_id) from {table1}
where ISSUE_PREFIX = 'TR' and {table5}.Activity = 'Tree Maintenance');
SQL: update {table5} set total_this_yr = (select count(issue_id) from {table1}
where ISSUE_PREFIX = 'LS' and {table5}.Activity like 'Landscape%');
SQL: update {table5} set total_this_yr = (select count(issue_id) from {table1}
where ISSUE_PREFIX = 'SB' and {table5}.Activity like '%Stand-By%');
SQL: update {table5} set total_this_yr = (select count(issue_id) from {table1}
where ISSUE_PREFIX = 'FM' and {table5}.Activity like '%Facilities%');

In this case, only the total for "FM stays in the table. the others go back to 0. If I leave off the code to load "FM", then only SB stays in the table, etc. It's only the last calculated field that stays.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-25 : 22:47:31

is this MS SQL Server ?

KH
[spoiler]Time is always against us[/spoiler]

mgossman
Starting Member

3 Posts

Posted - 2008-07-28 : 17:44:07

this is SQL Server 2005

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-28 : 18:21:29

That's because you don't have a WHERE clause in the UPDATE. You just have a WHERE clause in the subquery.

You should be using a join though rather than this subquery business.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

mgossman
Starting Member

3 Posts

Posted - 2008-07-28 : 19:23:25

That was it....THANX Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources