Or do you want duplicates, as in:
UPDATE "TABLE" t1
set "POS" = 1 + (SELECT COUNT(DISTINCT "MONTH")
FROM "TABLE" t2
WHERE t2."YEAR" = 2010 and t2."MONTH" < t1."MONTH")
WHERE t1."YEAR" = 2010
(using double quotes since your example contained lots of reserved words),
Set
2017-06-15 15:41 GMT+02:00 Svein Erling Tysvær <[email protected]>:
> It is possible to do this with a complex, but straight UPDATE statement.
> However, my hunch is that the below is simpler:
>
> execute block as
> declare variable i integer;
> declare variable PK <whatever>;
> i = 1;
> for select <PrimaryKey>
> from table
> where year = 2010
> order by month, <PrimaryKey>
> into :PK do
> begin
> update table
> set "POS" = i
> where <PrimaryKey> = :PK;
> i = i + 1;
> end
> end
>
> HTH,
> Set
>
> 2017-06-15 15:33 GMT+02:00 'Check_Mail' [email protected]
> [firebird-support] <[email protected]>:
>
>> Hello,
>>
>> How can I increment consisting records like this:
>>
>> Year month order pos
>> 2010 2 1 null
>> 2010 2 1 null
>> 2010 2 1 null
>> 2010 3 2 null
>>
>> Pos should be auto increment for all 2010 2 records, null to 1, the second
>> record null to 2 til 4
>>
>> Update table set pos = coalesce(pos,0)+1 where year = 2010 and month = 2
>> works not, all records have the pos=1 value
>>
>> Thank you
>>
>> Best regards
>>
>> Olaf
>>
>>
>> ------------------------------------
>> Posted by: "Check_Mail" <[email protected]>
>> ------------------------------------
>>
>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>
>> Visit http://www.firebirdsql.org and click the Documentation item
>> on the main (top) menu. Try FAQ and other links from the left-side menu
>> there.
>>
>> Also search the knowledgebases at http://www.ibphoenix.com/resou
>> rces/documents/
>>
>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>> ------------------------------------
>>
>> Yahoo Groups Links
>>
>>
>>
>>
>
