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:
I have a table with these columns
101-James-March-null -null -bbbb

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) )
 update [tableA] set DepositAmount = b.DepositAmount, checkORcash   = b.checkORcash
  From [tableB] b where month='March'        
set @i=@i+1
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)
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' )

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'


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