AW: [firebird-support] increment

2017-06-15 Thread 'Check_Mail' check_m...@satron.de [firebird-support] 
Thanks a lot 


Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Gesendet: Donnerstag, 15. Juni 2017 15:42
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] increment

  
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 ;
  i = 1;
  for select  
  from table
  where year = 2010
  order by month, 
  into :PK do
  begin
  update table
 set "POS" = i
  where  = :PK;
  i = i + 1;
  end
end

HTH,
Set

2017-06-15 15:33 GMT+02:00 'Check_Mail' mailto:check_m...@satron.de 
[firebird-support] <mailto:firebird-support@yahoogroups.com>:
Hello,

How can I increment consisting records like this:

Year  month   order pos
2010  2 1null
2010  2 1null
2010  2 1null
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" <mailto:check_m...@satron.de>


++

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/resources/documents/

++


Yahoo Groups Links

Re: [firebird-support] increment

2017-06-15 Thread Svein Erling Tysvær setys...@gmail.com [firebird-support] 
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 :

> 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 ;
>   i = 1;
>   for select 
>   from table
>   where year = 2010
>   order by month, 
>   into :PK do
>   begin
>   update table
>  set "POS" = i
>   where  = :PK;
>   i = i + 1;
>   end
> end
>
> HTH,
> Set
>
> 2017-06-15 15:33 GMT+02:00 'Check_Mail' check_m...@satron.de
> [firebird-support] :
>
>> Hello,
>>
>> How can I increment consisting records like this:
>>
>> Year  month   order pos
>> 2010  2 1null
>> 2010  2 1null
>> 2010  2 1null
>> 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" 
>> 
>>
>> ++
>>
>> 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
>>
>>
>>
>>
>

Re: [firebird-support] increment

2017-06-15 Thread Svein Erling Tysvær setys...@gmail.com [firebird-support] 
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 ;
  i = 1;
  for select 
  from table
  where year = 2010
  order by month, 
  into :PK do
  begin
  update table
 set "POS" = i
  where  = :PK;
  i = i + 1;
  end
end

HTH,
Set

2017-06-15 15:33 GMT+02:00 'Check_Mail' check_m...@satron.de
[firebird-support] :

> Hello,
>
> How can I increment consisting records like this:
>
> Year  month   order pos
> 2010  2 1null
> 2010  2 1null
> 2010  2 1null
> 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" 
> 
>
> ++
>
> 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/
> resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>

[firebird-support] increment

2017-06-15 Thread 'Check_Mail' check_m...@satron.de [firebird-support] 
Hello,

 

How can I increment consisting records like this:

 

Year  month   order pos

2010  2 1null

2010  2 1null

2010  2 1null

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

[firebird-support] increment

2017-06-15 Thread 'Check_Mail' check_m...@satron.de [firebird-support] 
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