Updating records with incremental counter

[emozley]

Hi I am trying to get some records in date order and then update a field OrderOnSchedule with an incremental counter - I can't see where I am going wrong?

DECLARE @counter int
SET @counter = 0

WITH    q AS
        (
        SELECT ScheduleDocuments.DocID, Documents.Title, 
        Documents.FolderID, Documents.DocDate, 
        ScheduleDocuments.OrderOnSchedule
        FROM ScheduleDocuments
        LEFT JOIN Documents ON ScheduleDocuments.DocID=Documents.DocID
        WHERE ScheduleDocuments.ScheduleID=10
        AND Documents.FolderID=121
        ORDER BY 
			Documents.DocDate DESC
        )
UPDATE  q
SET @counter = OrderOnSchedule = @counter + 1

Any ideas anyone?

Thanks!

Ed

 

[emozley]

Well after a bit of fiddling around I worked out I needed a semi colon and also I couldn't use order by in the sub query without using a SELECT TOP. The query now runs with any errors but is not ordering correctly:

DECLARE @counter int
SET @counter = 0;

WITH    q AS
        (
        SELECT TOP 100 PERCENT ScheduleDocuments.DocID, Documents.Title, 
        Documents.FolderID, Documents.DocDate, 
        OrderOnSchedule
        FROM ScheduleDocuments
        LEFT JOIN Documents ON ScheduleDocuments.DocID=Documents.DocID
        WHERE ScheduleDocuments.ScheduleID=10
        AND Documents.FolderID=121
        ORDER BY 
			Documents.DocDate DESC
        )
UPDATE  q
SET @counter = OrderOnSchedule = @counter + 1