I posted this in another forum but didn't get a response:

I'm trying to do the same with ListX as noted a couple of years ago in the general forum (slightly different with the following code - I added comments per the original post so you can see what I'm doing - and am getting an error).

First, the SQL...

DECLARE @HalfCount int /* As noted this is used for marking the first half of the records */

CREATE TABLE #tmpSeries
(
RowNumber [int] IDENTITY(1,1) NOT NULL,
SeriesID1 [int],
SeriesDescription1 [nvarchar](250),
SeriesID2 [int],
SeriesDescription2 [nvarchar](250),
)

INSERT INTO #tmpSeries (SeriesID1, SeriesDescription1)
SELECT TOP 50 PERCENT SeriesID, SeriesDescription
FROM Series
WHERE dbo.Series.SeriesID NOT IN (3, 20, 18, 7, 13, 26, 23)
ORDER BY SeriesDescription ASC
SELECT @HalfCount = SCOPE_IDENTITY() /* Retain RowNumber for last record written */

INSERT INTO #tmpSeries (SeriesID2, SeriesDescription2)
SELECT *
FROM ( /* As noted, here is the inner reversed statement */
SELECT TOP 50 PERCENT SeriesID, SeriesDescription
FROM Series
WHERE dbo.Series.SeriesID NOT IN (3, 20, 18, 7, 13, 26, 23)
ORDER BY SeriesDescription DESC) ReverseSeries
ORDER BY SeriesDescription ASC

/* Update the first half of the records with data from the second half */
UPDATE #tmpSeries SET
SeriesID2 = Half.SeriesID2,
SeriesDescription2 = Half.SeriesDescription2,
FROM #tmpSeries INNER JOIN (SELECT (RowNumber - @HalfCount) AS RowNumber, SeriesID2, SeriesDescription2 FROM #tmpSeries) Half
ON #tmpSeries.RowNumber = Half.RowNumber

/* Remove the extraneous half */
DELETE FROM #tmpSeries WHERE RowNumber > @HalfCount

SELECT * FROM #tmpSeries ORDER BY SeriesDescription1, SeriesDescription2

Now the error...
I keep getting an error in the UPDATE statement, specifically in the From record...

Msg 156, Level 15, State 1, Line 32
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'Half'.

Anything I'm missing? I've been working on a lot of other things and don't doubt I'm missing something obvious.

I'm using SQL Server 2005...

Thanks in advance.