[Phpwm] incrementing values during an update

[BTClick]

If memory serves, I did this on my old PostgreSQL box, should be something on 
other DBs.
You can use 2 methods, pretty much do the same thing:

1. Use a sequence:

CREATE SEQUENCE devcount_seq;
UPDATE tblprintq SET q_printed = 'A', printer = '$printer', conbatch 
=$condbatch, devcount = nextval('devcount_seq;) WHERE q_batch = $q_batch AND 
q_printed = 'N'

2. Use a serial defined column in the table definition:

CREATE TABLE tblprint (q_printed ..., printer ..., conbatch ..., devcount 
SERIAL,q_batch ..., q_printed ... );

Not sure how you are going to get the first value as NULL though.
HTH,
Colin

On Wednesday 16 Sep 2009 15:38:52 alan dunn wrote:
> I want to update a sub-set of records in a sql table. three of the
> values are fixed.
>
> 'devcount' I want to increment starting from 1 each time the query is
> run for a new $q_batch:
>
> UPDATE tblprintq SET q_printed = 'A', printer = '$printer', conbatch =
> $condbatch, devcount = ??????
>  WHERE q_batch = $q_batch
>  AND q_printed = 'N'
>
> I can do it by doing a SELECT to get the row ID, then incrementing a
> counter, then running an UPDATE on row ID, but is there a way of doing
> this within the query?
>
> Thanks, alan dunn
>
> _______________________________________________
> Phpwm mailing list
> Phpwm at mailman.lug.org.uk
> https://mailman.lug.org.uk/mailman/listinfo/phpwm