CODEWITHSUNDEEP
sqlsql-server
Abhijeet Parihar
Abhijeet Parihar

Reputation: 13

T- SQL Update Issue

There are two Tables A and Table B

Table A

Date          Budget
9-1-2016        21
9-2-2016        10

Table B

Date          Amount      Budget
9-1-2016        12
9-1-2016        15
9-1-2016        17
9-2-2016        15
9-2-2016        10
9-3-2016        12

I want to divide the daily budget in Table A to the corresponding items in Table B as follows:

Date          Amount      Budget
9-1-2016        12          7
9-1-2016        15          7
9-1-2016        17          7
9-2-2016        15          5
9-2-2016        10          5
9-3-2016        12          0

For example, the budget of 9-1-2016 in Table A is divided into the 3 items in Table B which have the same date.

Upvotes: 1

Views: 32

Answers (1)

McNets
McNets

Reputation: 10807

This should do the job:

Note I've used a LEFT JOIN with table A, just to take care of nonexistent Budgets.

update tableB
set Budget = coalesce(a.Budget,0) / c.numB
from tableB b
     left join tableA a on a.Date = b.Date
     inner join (select Date, count(*) as numB
                 from tableB
                 group by date) c on c.Date = b.Date;

Upvotes: 2

Related Questions