Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1251

Re: SQL Cursors

Show all headers | View raw

sim wrote:
> On 2012-09-05 10:18:34 +0000, Bob Barrows said:
>
>> sim wrote:
>>> On 2012-09-04 15:27:18 +0000, Bob Barrows said:
>>>
>>>> simisa wrote:
>>>>> Hallo all,
>>>>> I have a problem to resolv asap...I hope that somebody can help me
>>>>>
>>>>> 1 Table called classage
>>>>>
>>>>> agent1     10000   value2     1
>>>>> agent2     8000     value2     2
>>>>> agent3     7500     value2     3
>>>>> agent4     1000     value2     4
>>>>> ...
>>>>>
>>>>> The table is order by rank desc.
>>>>> I need to update the value2 like this:
>>>>>
>>>>> agent1     10000   0             1
>>>>> agent2     8000     2000       2
>>>>> agent3     7500     500         3
>>>>> agent4     1000     6500       4
>>>>>
>>>>>
>>>>> Is it possible ?
>>>>>
>>>> Yes, it is certainly possible. Without cursors.
>>>>
>>>> What version of sql server (this is very relevant for this
>>>> question)? What are the column names and datatypes? What is the
>>>> primary key of the table? You can best answer the latter two
>>>> questions by using SSMS (or EM, depending on your sql version) to
>>>> generate the table-creation DDL script for the table and posting
>>>> it.
>>>
>>> Hallo,
>>> here we are:
>>>
>>> Table CLASSAGE:
>>>
>>> agents varchar(50) Checked
>>> netto decimal(38, 2) Checked
>>> netto2 decimal(38, 2) Checked
>>> num int Checked
>>>
>>>
>>> where num is the ranking and netto2 is the calculated value to
>>> obtain. No ID/Identity.
>>>
>>
>> You failed to answer my very first question!
>> What version of sql server are you using? I really do need to know
>> this!
>
> Sorry !!! it's a sql2000 sp3
>
> I work with DTS.
>
> I have the possibility to work on sql2008r2, not a problem.
>
With 2008, a CTE can  be used. This is not the case in 2000, but the 
solutions are very similar. It involves a self-join:

update c
set netto2 = coalesce(q.netto,c.netto) - c.netto
from classage as c join
(select netto,num from classage) as q
on c.num = q.num + 1

It is not recommended to store calculated values this way. This value can be 
generated on the fly using a similar select statement. My advice would be TO 
DROP that netto2 column from CLASSAGE and create a view to calculate it:

create view vCLASSAGE AS
SELECT agents
, c.netto
, coalesce(q.netto,c.netto) - c.netto
, c.num
from classage as c join
(select netto,num from classage) as q
on c.num = q.num + 1

That way it will always be correct and you will never have to run extra code 
to update that column. 

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

SQL Cursors simisa@tiscalinet.it (simisa) - 2012-09-04 14:29 +0200
  Re: SQL Cursors "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2012-09-04 11:27 -0400
    Re: SQL Cursors sim <simisa@tiscalinet.it> - 2012-09-05 08:32 +0200
      Re: SQL Cursors "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-09-05 06:18 -0400
        Re: SQL Cursors sim <simisa@tiscalinet.it> - 2012-09-05 13:00 +0200
          Re: SQL Cursors "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-09-05 07:45 -0400
            Re: SQL Cursors sim <simisa@tiscalinet.it> - 2012-09-05 18:19 +0200
            Re: SQL Cursors rja.carnegie@gmail.com - 2012-09-05 15:36 -0700
              Re: SQL Cursors "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-09-05 18:44 -0400
                Re: SQL Cursors rja.carnegie@gmail.com - 2012-09-05 16:58 -0700
                Re: SQL Cursors Gene Wirchenko <genew@ocis.net> - 2012-09-05 17:23 -0700
                Re: SQL Cursors rja.carnegie@gmail.com - 2012-09-06 02:50 -0700
              Re: SQL Cursors "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-09-05 18:44 -0400
                Re: SQL Cursors sim <simisa@tiscalinet.it> - 2012-09-06 09:37 +0200

csiph-web