> Hi all, i m making a little website for a store using firebird 1.5 and
> php, and it have a stock control table.. so i m using a table with the
> following fields:
>
> id, product_id, action(add or remove), quantity and actual_stock
>
> its kinda simple, my table data is like this:
>
> id, pro_id, action, quantity, actual_stock
> 1, 01, A, 10, 0
> 2, 01, R, 1, 9
> 3, 01, R, 1, 8
> 4, 01, R, 1, 7
> 5, 01, R, 2, 5
> 6, 01, A, 5, 10
>
> but my problem is... i have to somethimes remove a row, and i have to
> rewrite all the atual_stock of the product.. like, if i remove the
> second entry.. i have to do it:
>
> id, pro_id, action, quantity, actual_stock
> 1, 01, A, 10, 0
> 2, 01, R, 1, 9<-Remove
> 3, 01, R, 1, 9<-Update
> 4, 01, R, 1, 8<-Update
> 5, 01, R, 2, 6<-Update
> 6, 01, A, 5, 11<-Update
>
> So it have to update the rows after the one that was removed.. but i
> dont have idea how to do it... my table should look like this after
> the previuos delete:
>
>
> id, pro_id, action, quantity, actual_stock
> 1, 01, A, 10, 0
> 3, 01, R, 1, 8
> 4, 01, R, 1, 7
> 5, 01, R, 2, 5
> 6, 01, A, 5, 10
>
> btw. i cant figure out how to do it with firebird, i tried to think in
> a solution but there are too many stuff to update.. as i m noob in
> databasesi dont know how
>
> Please, any help and ideas are welcome, hope you understand where i m
> trying to get at ^^
>
> Thanx in advance,
>
> F�bio.
>
>

You definitely do NOT want to do what you sre trying to do in this manner.

If you want the actual_stock so that you can know what you have on hand at
any time you could do this in several different ways.

Drop the actual_stock column from this table. As you have found out this
will cause you headaches. You could use Quantity as a signed value such
that a removed value of 2 was -2. You could then 'A'dd and 'R'emove rows
without any updating. In fact you don't even need the Action column as this
information is known by the sign. You get the stock on hand with the
following query.

SELECT Pro_Id, sum(Quantity) On_Hand
FROM StockControl
WHERE Pro_Id = :Pro_Id


If you need a multiple row set with a running total of On_Hand then you
could write a stored procedure. There is an example in Helen's book.


Bill Meaney