从另一个表中的列更新列值

I have two tables 1) Weekly hits 2) Monthly hits

我有两张表1)每周点击2)每月点击

Every week for a month a column in the monthly hit will increment itself using data form the weekly hit table.

在一个月的时间里，每个星期，每月命中的一列将使用每周命中表的数据来增加自己。

The weekly hits table has information that looks like this:

每周点击量表的信息如下:

Name   Date      TypeofHit     Location

Jeff  10-10-12      Bunt       New York
Sally 10-12-12      Bunt       Wyoming
 Joe  10-12-12      Hit        Texas
Mark  10-14-12      Hit        Ohio
James 10-15-12      Miss       California

The Monthly table is structured like this.

月度数据表的结构是这样的。

TypeofHit   TCount
   Bunt       2
   Hit        2
   Miss       1

What I want to to do is add total TypesofHits from the Weekly table into the Monthly Table

我想做的是将每周的总点击量添加到每月的列表中

SELECT COUNT(*) From WEEKLY GROUP BY TypeofHit

The above gives me the numbers I need to add to the Monthly table, but I am having trouble with the proper formatting with the Update statement. I have tried

上面给出了我需要添加到月度表中的数字，但是我在使用Update语句进行正确格式化时遇到了麻烦。我有试过

UPDATE Monthly SET TCount + (SELECT Count(*) from Weekly GROUP BY TypeofHit) WHERE Monthly.TypeofHit = Weekly.TypeofHit

I am using MSSMS

我用MSSMS

4 个解决方案

#1

You need to JOIN both tables:

您需要同时加入两个表:

UPDATE M
SET M.TCount = M.TCount + W.TCount
FROM Monthly M
INNER JOIN (SELECT  TypeofHit,
                    COUNT(*) TCount
            FROM Weekly
            GROUP BY TypeofHit) W
    ON M.TypeofHit = W.TypeofHit

And, by the way, I'm missing the date on the monthly table, you need to make sure to join them by that column too for this to make any sense.

顺便说一下，我错过了月度表格上的日期，你需要确保把它们也加入到这个列中，这样才有意义。

#2

I think you are looking for a update statement for updating the monthly table, then you can use the below

我认为您正在寻找更新语句来更新月度表，然后您可以使用下面的语句

UPDATE M
SET M.TCount = M.TCount + A.TypeCount
FROM Monthly M 
JOIN (
select count(1) TypeCount, TypeofHit   from Weekly GROUP BY TypeofHit
) A
on A.TypeofHit = M.TypeofHit

#3

Try with this query (here an EXAMPLE), this works perfectly:

尝试使用这个查询(这里有一个示例)，它非常有效:

WITH tempTable1 AS
(
    SELECT TypeofHit, COUNT(*) as c FROM Weekly
    GROUP BY TypeofHit
)
UPDATE Monthly  
SET Monthly.TCount = Monthly.TCount + T2.c
FROM Monthly
INNER JOIN tempTable1 T2 ON Monthly.TypeofHit = T2.TypeofHit

#4

UPDATE a
   SET a.TCount = a.TCount + b.TCount
  FROM Monthly a,(  select TypeofHit, count(*) TCount   
                      from WEEKLY 
                  GROUP BY TypeofHit
                 ) b
 WHERE a.TypeofHit = b.TypeofHit

#1