Subject Re: [firebird-support] increment
Author Svein Erling Tysvær
Post date 2017-06-15T13:48Z
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 <setysvar@...>:
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' check_mail@... [firebird-support] <firebird-support@yahoogroups. com>:
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" <check_mail@...>
------------------------------ ------

++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++ ++++++

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

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/ firebird-support/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/ firebird-support/join
    (Yahoo! ID required)

<*> To change settings via email:
    firebird-support-digest@yahoog roups.com
    firebird-support-fullfeatured@ yahoogroups.com

<*> To unsubscribe from this group, send an email to:
    firebird-support-unsubscribe@y ahoogroups.com

<*> Your use of Yahoo Groups is subject to:
    https://info.yahoo.com/legal/u s/yahoo/utos/terms/