如何使用另一个表数据中的多个值更新客户？ [英] How to update a customer with multiple values in another table data ?

问题描述

我有一张包含这些栏目的表格

[tableA]：

CID-CName-Month-DepositAmount-checkORcash- colB

101-James-March-null -null -bbbb



[tableB]：

CID- Month-DepositAmount-checkORcash

101-March-100.00 - 现金

101-March-200.00 - 现金

101-March-300.00 - 现金

101-April-200.00 - 现金

101-April-250.00 - 支票





这里我想更新[tableA] DepositAmount，checkORcash with [tableB] DepositAmount，checkORcash。

你能帮我解决一下如何更新[tableA]。我想在[tableB]中添加所有DepositAmount，checkORcash值，其中month ='March'



我尝试过：

声明@i int 
设置@i = 1 
 while（@ i< =（select count（*） FROM [tableB]，其中Month ='March'且CID = 101））
 begin 
 update [tableA] set DepositAmount = b.DepositAmount，checkORcash = b.checkORcash 
来自[tableB] b其中month ='March'
 set @ i = @ i + 1 
 end 
 go 

解决方案

< blockquote>如果您认为需要使用SQL循环，那么请再想一想！



您需要的是（未经测试）

  update   set  DepositAmount = b.DepositAmount，checkORcash = b.checkORcash 
 来自 tableA A 
  left   join  tableB B  on  A.CID = B.CID 
  WHERE  A. [month] =  '  March' 

始终记住基于RDMS的数据库（SQL数据库）是基于SET的。您可以描述您希望在一组数据中发生的事情，而不是一次一行。这篇文章（是的，它是我的）说了我想说的一切 - 处理循环SQL Server [ ^ ]

我认为你不想更新，你想要将表B中的所有行军记录添加到A，因为有多个记录带有游行。

只需将您的解决方案修改为，





如果存在（选择前1 * FROM [tableB]，其中Month ='March '）

开始



插入[tableA]（CID，CName，Month，DepositAmount，checkORcash）选择b.CID，（选择名称来自[Customer_table]，其中id = b.CID），b.Month，b.DepositAmount，b.checkORcash来自[tableB] b，其中Month ='March'



end



我理解你的问题我提供了上述解决方案，

让我知道问题是否与解决方案有关。

hi,
I have a table with these columns
[tableA]:
CID-CName-Month-DepositAmount-checkORcash-colB
101-James-March-null -null -bbbb

[tableB]:
CID-Month-DepositAmount-checkORcash
101-March-100.00 - cash
101-March-200.00 - cash
101-March-300.00 - cash
101-April-200.00 - cash
101-April-250.00 - check


here I would like to update the [tableA] DepositAmount, checkORcash with [tableB] DepositAmount, checkORcash.
Can you please help me how to update a [tableA]. I want to add all the DepositAmount, checkORcash values from [tableB] where month='March'

What I have tried:

declare @i int 
set @i = 1
while (@i <=  (select count(*) FROM [tableB] where Month='March' and CID=101) )
begin
 update [tableA] set DepositAmount = b.DepositAmount, checkORcash   = b.checkORcash
  From [tableB] b where month='March'        
set @i=@i+1
end
go

解决方案

If you ever think you need to use a loop with SQL then think again!

What you need is something like (untested)

update A set DepositAmount = b.DepositAmount, checkORcash   = b.checkORcash
from tableA A
left join tableB B on A.CID=B.CID
WHERE A.[month] = 'March'

Always remember that RDMS based databases (SQL databases) are "SET" based. You describe what you want to happen to a set of data, not a row at a time. This article (yes, it's mine) says everything I'm trying to say - Processing Loops in SQL Server[^]

I think you don't want to update, you want to add all the march records from table B to A because there are multiple records with march .
Just modify your solution as,


if exists(select top 1 * FROM [tableB] where Month='March' )
begin

insert into [tableA] (CID,CName,Month,DepositAmount,checkORcash) select b.CID,(select name from [Customer_table] where id=b.CID),b.Month,b.DepositAmount,b.checkORcash from [tableB] b where Month='March'

end

AS I understand your question I provide the above solution,
let me know if issue is with solution.

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