基于另一个表中的数据更新一个SQL表 [英] Updating one SQL table based on data in another table

问题描述

我正在运行Microsoft SQL Server 2008 R2，并从两个表中提取信息以创建一个新表。

I am running Microsoft SQL Server 2008 R2, and pulling information from two tables to create one new table.

表A具有唯一的潜在客户编号和其他信息。

表B具有唯一销售编号的销售， 。

这两个表中的数据被拉入SQL Server中的临时表，所以我可以更改和更新任何我需要的，并且这将输出到一个新的表。

Table A has leads with a unique lead number and other information.
Table B has sales with a unique sales number, and the lead number associated with it.
Data from both tables are pulled into temp tables in SQL Server so I can change and update whatever I need, and the output of this will go into a new table.

表A中的一个潜在客户可以在表B中拥有多个与其相关联的销售。

One lead from Table A can have multiple sales associated with it in table B.

基于表B（销售）中显示的潜在客户编号的次数，表A（潜在客户）中的销售数量列。因此，如果表B（销售）的销售线索号码绑定到七（7）销售，表A（销售线索）中的销售数量列将更新为7.

I want to update the Number of Sales column in Table A (Leads) based on how many times that lead number appears in Table B (sales). So if Table B (sales) has a lead number tied to seven (7) sales, the Number of Sales column in Table A (leads) will be updated to 7.

我试过几个变体使用COUNT函数，但没有成功。任何帮助将不胜感激。

I have tried a few variations using the COUNT function but with no success. Any help would be appreciated.

推荐答案

假设字段名为 leadNo

update tablea 
set sales = (select count(*) 
             from tableb 
             where tableb.leadNo = tablea.leadNo)

SQL Fiddle演示

这篇关于基于另一个表中的数据更新一个SQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！