Update problem

[ChrisH2]

I am having great difficulty trying to do a update without using a cursor.

I want to update Table B reducing its qty(s) by the qty in Table A. I am also not allowed to modify the qty in Table A.

I don't see a way of doing it because it needs to effect multiple rows.

It's quite easy to do using a cursor, but im trying to come away from using cursors.

Is a temporary table the way to go?
Can anyone point me in the right direction.


Table A

Code Qty
---- ---
A 20
B 50
C 35


Table B Before update

Code Line Qty
---- ---- ----
A 1 5
A 2 12
A 3 14
B 1 40
B 2 30
C 1 50


What table B should look like after update

Code Line Qty
---- ---- ----
A 1 0
A 2 0
A 3 11
B 1 0
B 2 20
C 1 15

 

[vbkris]

why not use joins in update?

sample from BOL:

UPDATE titles
SET t.ytd_sales = t.ytd_sales + s.qty
FROM titles t, sales s
WHERE t.title_id = s.title_id


Known is handfull, Unknown is worldfull

 

[vbkris]

ah, sorry

didnt take a closer look. that will not help, let me see if i can find another alternative...

Known is handfull, Unknown is worldfull

 

[mutley1]

Chris,

The results seem a bit strange - I assume you don't want to go negative so minimum result is 0, but A3 (14) minus 20 gives 11 (?) and B2 (30) minus 50 gives 20?

 

[vbkris]

nope, he adds up all three A entries and reduces them accordingly.

Code Line Qty
---- ---- ----
A 1 5
A 2 12
A 3 14

A = 5 + 12 + 14 = 31 - 20 (In table B) = 11

the rows are reduced on a FIFO basis...

Known is handfull, Unknown is worldfull

 

[mutley1]

ah - gotcha!

 

[ChrisH2]

Hi mutley1,

It needs to use up the quantites in table B.
Table B has a total of 31 for Code A, 70 for code B and 50 for code C.