SQL Update Multiple Rows with Count from another Table

Question

Suppose I have two tables. One table, tbl1, is "long" and non-aggregated. The structure is as follows:

Software_Name:
Word
PowerPoint
PowerPoint
Excel
Word
PowerPoint

In a second Table, tbl2, I want to summarize the data from the first table, namely the count of Software. The second Table will have a structure like:

Software_Name:      Count:
Word                 2
PowerPoint           3
Excel                1

I have tried:

update tbl2
set count = 
(select count(software_name)
from tbl1
group by software_name
where tbl1.software_name = tbl2.software_name)
from tbl1

I get a result inserted into the proper column, but it is not the proper value. It is the sum of all values, in this case 5. I have included the where clause because in my tbl1 I have many more software_names than am interested in counting in tbl2.

UPDATE I am using Teradata Aster for this project. I have been looking at the Aster documentation for the UPDATE command and came across this:

UPDATE [ ONLY ] table
   SET column = expression [, ...]
   [ FROM fromlist ]
   [ WHERE condition | WHERE CURRENT OF cursor_name ];

In reading about the fromlist, I came across this bit of information:

Note that the target table must not appear in the fromlist unless you intend a self-join (in which case it must appear with an alias in the fromlist).

Your where is after your group by...I don't know what dbms allows that...but putting it before the group by should solve it — Ctznkane525, Jun 23 '18 at 00:49

score 2 · Answer 1 · answered Jun 22 '18 at 23:19

2

You want a correlated subquery:

update tbl2
    set count = (select count(*) from tbl1 where tbl1.software_name = tbl2.software_name);

answered Jun 22 '18 at 23:19

Gordon Linoff

1,242,037
58
646
786

I like this solution. Unfortunately, I get a column name does not exist error despite the column being in the table. Any insights into why that might happen? – Daniel Jun 23 '18 at 01:19
@Daniel . . . There must be a misspelling or something. – Gordon Linoff Jun 23 '18 at 01:29
unfortunately it doesn't look like a misspelling. I am using Aster if that makes a difference. I'm unable to find any documentation that talks about the error. – Daniel Jun 23 '18 at 02:15

score 0 · Answer 2 · answered Jun 22 '18 at 23:20

Not sure what DBMS you're using but in SQL Server I would recommend a CROSS APPLY with example below...

update t2
set t2.[Count] = t1.[Count]
from t2
    cross apply (
                    select count(*) AS [Count]
                    from t1
                    where t1.Software_name = t2.Software_name
                ) AS t1

You can read more about the APPLY operator here: https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

Mallikh · Answer 3 · 2018-06-23T00:45:37.170

You can try using the CTE as below:

DECLARE @TABLE1 AS TABLE (Software_Name VARCHAR(100))

INSERT INTO @TABLE1
SELECT 'Word'
UNION ALL
SELECT 'PowerPoint'
UNION ALL
SELECT 'PowerPoint'
UNION ALL
SELECT 'Excel'
UNION ALL
SELECT 'Word'
UNION ALL
SELECT 'PowerPoint'



 DECLARE @TABLE2 AS TABLE (Software_Name VARCHAR(100),Cnt INT)
 INSERT INTO @TABLE2 (Software_Name)
 SELECT DISTINCT Software_Name FROM @TABLE1



 ;WITH CTE AS
 (
     SELECT 
         COUNT(T1.Software_Name) AS Cnt
         ,T1.Software_Name 
     FROM @TABLE2 T2
     INNER JOIN @TABLE1 T1 ON T1.Software_Name = T2.Software_Name
     GROUP BY
     T1.Software_Name
 )


 UPDATE T2
 SET 
 T2.Cnt =  C.Cnt
 FROM @TABLE2 T2
 INNER JOIN CTE C ON C.Software_Name = T2.Software_Name

 SELECT * FROM @TABLE2

NOTE: I am assuming you are using SQL Server. Also i am posting it as an answer as i dont have desired reputation to comment.

hope it helps!

SQL Update Multiple Rows with Count from another Table

3 Answers3