在MS SQLServer中交换两行,保留原始主键,无需手动更新

-- 创建临时表
SELECT TOP 0 * INTO #TempTable FROM YourTable

-- 将需要交换的两行数据插入临时表
INSERT INTO #TempTable
SELECT TOP 2 * FROM YourTable WHERE SomeCondition ORDER BY SomeColumn

-- 交换两行的数据
UPDATE YourTable
SET SomeColumn = (SELECT SomeColumn FROM #TempTable WHERE SomeCondition)
WHERE SomeCondition

-- 删除临时表
DROP TABLE #TempTable

;WITH CTE AS (
    SELECT TOP 2 * FROM YourTable WHERE SomeCondition ORDER BY SomeColumn
)
UPDATE YourTable
SET SomeColumn = (SELECT SomeColumn FROM CTE WHERE SomeCondition)
WHERE SomeCondition

;WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY SomeColumn) AS RowNumber FROM YourTable WHERE SomeCondition
)
UPDATE CTE
SET SomeColumn = (SELECT SomeColumn FROM CTE WHERE RowNumber = 2)
WHERE RowNumber = 1