How to count the number of values ​​in a table with 2 columns as a parameter on SQL Server?

Question

I have 2 tables where the values ​​are as follows:

Table a:

|     Date     |     Time     |     Count     |
===============================================
|      D1      |      T1      |       0       |
|      D1      |      T2      |       0       |
|      D2      |      T1      |       0       |
|      D2      |      T2      |       0       |

Table B:

|     Date     |     Time     |     Value     |
===============================================
|      D1      |      T1      |       a       |
|      D1      |      T1      |       b       |
|      D1      |      T1      |       c       |
|      D1      |      T2      |       e       |
|      D2      |      T1      |       f       |
|      D2      |      T1      |       g       |
|      D2      |      T1      |       h       |
|      D2      |      T2      |       i       |
|      D2      |      T2      |       j       |

I am trying to create a query that could count the quantity Valuefrom Table Band insert the result into a column CountfromTable A

Where the result should show (in table A):

|     Date     |     Time     |     Count     |
===============================================
|      D1      |      T1      |       3       |
|      D1      |      T2      |       1       |
|      D2      |      T1      |       3       |
|      D2      |      T2      |       2       |

I tried using regular UPDATE with code:

UPDATE A
SET Count = (SELECT COUNT(Value)
             FROM B, A
             WHERE A.Date = B.Date
                   and A.Time = B.Time)

However, this led to the fact that all Count became 9 instead of showing each date and time, its value set

I could not create an identifier for each date and time, as the values ​​of the Date and Time column will change every hour

Is there something wrong with my code, or is there any other way to get closer to this?

Thank you in advance for your help.

Answer 1

SQL , - . , - tableB.

UPDATE tableA 
SET Count = b.Count
FROM tableA a
INNER JOIN
(
    SELECT Date, Time, COUNT(value) as Count
    FROM tableB
    GROUP BY Date,Time
) b
ON a.Date = b.Date AND a.Time=b.Time

Live demo: http://sqlfiddle.com/#!18/7264f/2

Answer 2

JOIN. [Value] .

Query

update t1
set [t1].[Count] = [t2].[Count]
from [TableA] as [t1]
join (
    select [Date], [Time], count([Value]) as [Count]
    from [TableB]
    group by [Date], [Time]
) as [t2]
on [t1].[Date] = [t2].[Date]
and [t1].[Time] = [t2].[Time];

Answer 3

, :

UPDATE A
SET Count = (SELECT COUNT(Value)
             FROM B, A --<-- A being used here overrides `A` of `UPDATE`
             WHERE A.Date = B.Date
                   and A.Time = B.Time)

A. COUNT A B. , COUNT A.

The original requested request, i.e. the one that was made before editing in the OP was right:

UPDATE A
SET Count = (SELECT COUNT(Value)
             FROM B
             WHERE A.Date = B.Date
                   and A.Time = B.Time)

In this query, there is a correlation between the updated row and all rows from Bbased on the condition:

A.Date = B.Date and A.Time = B.Time

Therefore, the value returned by the subquery depends on the fields Dateand Timeeach updated row.

Demo here