row number

update [Costing].[dbo].[Supplier]

set n =

(

SELECT (ROW_NUMBER() OVER (ORDER BY suppliercode) + 1000 )

as RowNumber from [Costing].[dbo].[Supplier] as t

where t.suppliercode = Supplier.suppliercode

)

hi i cannot update a column with the row number,

it is all taking 1001, supposed to be 1001,1002 and so on .

thanks.

Hi,

Use better something like this here:

update [Costing].[dbo].[Supplier]

set n = Subquery.RowNumber

FROM [Costing].[dbo].[Supplier] S

INNER JOIN (

SELECT (ROW_NUMBER() OVER (ORDER BY suppliercode) + 1000 )

as RowNumber from [Costing].[dbo].[Supplier] as t

) Subquery

ON Subquery.suppliercode = S.suppliercode

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

thanks it works , i did it shortcut way , not a good solution though.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER procedure [dbo].[DebugCursor]

as

BEGIN

SET NOCOUNT ON;

DECLARE cursorDebug Cursor FOR Select suppliercode

From supplier

Declare @.a nvarchar(25)

declare @.c int

set @.c = 300

Open cursorDebug Fetch NEXT FROM cursorDebug INTO @.a

While @.@.FETCH_STATUS =0

BEGIN

Update supplier Set n = @.c

Where suppliercode = @.a

set @.c = @.c + 1

Fetch NEXT FROM cursorDebug INTO @.a

END

CLOSE cursorDebug

DEALLOCATE cursorDebug

END

|||YOu should always prefer set based operations.

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.desql