SQLTeam.com | Weblogs | Forums

Update rows in line with total


#1

Hi all,

I have a product table:

 ID      Desc      No. of lines     old/new
 1       prod1     5                old
 2       prod2     4                old

etc

I have 10 rows in product table.

i want to grow the product table eg - to 25 new rows = so that means its a 250% increase in product range.

but the new lines added (rows 11 to 35) will be copies of the original 10. there will be duplicates. thats fine, but its the no of lines which is the concern.

for example lets say the original 10 rows had a sum of 50. if i copy over randomly and get to 35 rows, ive increase the lines by 250% but the total sum on lines most likely will be over 350%. i want the sum of lines to increase exactly by 250%, so total will be 125. i dont want to update the original 10 rows, ive tried to update new products by minusing 1 from no of lines to bring the increased percentage down but its a long process and temperamental.

any thoughts?


#2

Which is the column whose sum adds up to 50? In your example, I see a "number of lines" column. Is that what you are adding up?

If you want to make 25 copies using any combination of the 10 and the sum should exactly add up to 250, it may or may not be possible depending on the values in the existing rows. For example, if one of the existing rows had 41 and the remaining had 1 each, what combination of those would add up to 250 while requiring that there be exactly 25 of those that can be used?


#3

@JamesK "number of lines" column adds up to 50. i have 10 rows, only showing first 2 above.

i need to add 15 rows but the "number of lines" of all 25 rows need to add up to 125 (not 250). 250 is the percentage increase from 10 rows to 25 rows.

i was thinking it might not work - similar to what you have said.

if i had a loop adding from the original 10, until the total number of lines equals 125 then i will most likely have less than 25 rows. if i add 15 rows (copies of random original 10), then i would have hit the 250% growth increase but i bet the sum of number of lines will go over 125.

fyi - 125 is the target because original 10 sum lines = 50, plus 250% = 125.