Update column value with column of another table based on condition in SQL Server

smile
 
on Nov 11, 2021 02:28 AM
488 Views

I have two tables like that.

tblPurchase:

AccountID

InvNo

P_Date

ItemCode

Price

Qty

NetBal

A

Inv-1

11.11.2021

A

100

2

600

A

Inv-1

11.11.2021

B

100

2

600

A

Inv-1

11.11.2021

C

100

2

600

B

Inv-2

11.11.2021

A

100

1

200

B

Inv-2

11.11.2021

B

100

1

200

Account holder A purchased some items an amount of Rs. 600

Account holder B purchased some items an amount of Rs. 200 as you can see in the table.

Now I also want to update the Account status of Account Holder A and Account Holder B. here is the table tblAccount

AccountID

Balance

Address

A

2400

 

B

2000

 

C

1500

 

When an Account holder A purchased items of any price for example Rs. 600 Then this amount must be updated in his account at the tblAccount against Account holder A.  (2400 + 600 = 3000)

When an Account holder B purchased items of any price for example Rs. 200 Then this amount must be updated in his account at the tblAccount against Account holder B.  (2000 + 200 = 2200)

Now output in tblAccount Should be like that

tblAccount

Account

Balance

Address

A

3000

Abc

B

2200

Sky

C

1500

xyz

Please guide me how to get solution. If there is any better idea and structure then please guide me.

Download FREE API for Word, Excel and PDF in ASP.Net: Download