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).

Answer 1

You want a correlated subquery:

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

Answer 2

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/

Answer 3

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!