x

update the result of a query by looping it in another

one table led is having anum, adate, sno. for each anum there will be many records with diff adate and sno. I need a query to update the sno starting from 1 to max as per the ascending order of adate for each anum. probably multiple looping. can anybody help me out??
more ▼

asked Mar 18 '11 at 12:44 AM in Default

sky1982 gravatar image

sky1982
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Sounds like a job for a windowing/partitioning function, such as ROW_NUMBER. A SELECT statement that you can then tweak to do the update might look something like:

SELECT anum, adate, ROW_NUMBER() OVER (PARTITION BY anum ORDER BY adate) AS sno
FROM ...
...

A neat way of writing the UPDATE statement is to use a CTE, as [described here][2].

[2]: http://social.msdn.microsoft.com/Forums/en/transactsql/thread/ee06f451-c418-4bca-8288-010410e8cf14
more ▼

answered Mar 18 '11 at 12:58 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.4k 14 20 44

thanks alot....... i hope that worked. i am working further on it and i will be back soon.
Mar 22 '11 at 11:58 PM sky1982
(comments are locked)
10|1200 characters needed characters left

Is this the sort of thing you are describing?

DECLARE @led TABLE
    (
      anum INT ,
      adate DATE ,
      sno INT
    )
DECLARE @results TABLE
    (
      anum INT ,
      adate DATE ,
      sno INT
    )

WHILE ( SELECT
            COUNT(*)
        FROM
            @led
      ) < 50 
    INSERT  INTO @led
            ( [anum] ,
              [adate] ,
              [sno] 
            )
    VALUES
            ( ABS(CHECKSUM(NEWID())) % 8 + 1 , -- anum - int
              DATEADD(d, ABS(CHECKSUM(NEWID())) % 100 + 1 * -1, GETDATE()) , -- adate - date
              NULL  -- sno - int
            )

INSERT  INTO @results
        ( [anum] ,
          [adate] ,
          [sno] 
        )
        SELECT
            [l].[anum] ,
            [l].[adate] ,
            ROW_NUMBER() OVER ( PARTITION BY l.[anum] ORDER BY l.[adate] ASC )
        FROM
            @led AS l

SELECT
    *
FROM
@results AS l
more ▼

answered Mar 18 '11 at 01:47 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

(comments are locked)
10|1200 characters needed characters left

I'm not sure I follow what you are trying to achieve, but I can say for almost certain that you will not need to loop. SQL Server and other databases should operate on sets whenever possible (it's almost always possible).

Please post more details about your errand: - Table definitions - Relationships - If possible, some sample data - Desired result
more ▼

answered Mar 18 '11 at 12:49 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
15.9k 15 19 32

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1936
x128
x46
x23

asked: Mar 18 '11 at 12:44 AM

Seen: 882 times

Last Updated: Mar 18 '11 at 12:44 AM