Click here to Skip to main content
15,882,278 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted
Updated 4-May-17 21:17pm

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

What you need is something like (untested)
SQL
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[^]
 
Share this answer
 
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.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900