Updating a column with a count from another table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Updating a column with a count from another table?

Author

Topic

new2sqlplus
Starting Member

1 Post

Posted - 2014-05-18 : 21:49:36

My goal is to with one update statement, fill TABLE1.counter (currently empty) with the total count of TABLE2.e# (employee). Also, if TABLE1.e# isn't in TABLE2.e# then it sets it to "0" (TABLE1.e# 8 and 9 should have a counter of 0)
This is for sqlplus.

e.g. TABLE2:
e#
--
1
2
3
4
5
5
6
7
7
1
2
3
4
5

UPDATE TABLE1
SET counter = (
SELECT COUNT(TABLE2.e#)
FROM TABLE2 INNER JOIN TABLE1 ON (TABLE2.e# = TABLE1.e#)
GROUP BY TABLE2.e#);
--^Doesn't work

so my TABLE1 should be:
e# counter
-----------
1 .. 2
2 .. 2
3 .. 2
4 .. 2
5 .. 3
6 .. 1
7 .. 2
8 .. 0
9 .. 0

(The .. is just spacing to show the table here)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-19 : 13:57:51

UPDATE t1
SET [counter] = CASE WHEN t2.e# IS NOT NULL THEN t2.[counter] ELSE 0 END
FROM TABLE1 t1
LEFT JOIN (SELECT e#, COUNT(*) as [counter] FROM TABLE2 GROUP BY e#) t2
ON t1.e# = t2.e#

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources