在SQL中，我希望将特定行更新为新值
提问于 2016-02-18 18:59:57
使用SQL数据库时，我希望将表(workflowoutputcadas)中的一列(nonexcludedsites )更新为某些行(40-50)的特定值。我做的是这样的：
update workflowoutputcadas
set nonexcludedsites = 1
FROM
(SELECT ROW_NUMBER()
OVER (ORDER BY nonexcludedsites) AS Row,
nonexcludedsites
FROM workflowoutputcadas) AS w
WHERE Row between 40 and 50因此，对于这个示例，我希望将第40-50行更新为1，其余行保持不变。当我运行脚本时，它最终将整个列更新为1，这不是我想要做的。你知道我在哪里弄错了吗？
回答 2
Stack Overflow用户
发布于 2016-02-18 19:07:01
这样如何：
;WITH CteData
AS
(
SELECT ROW_NUMBER()
OVER (ORDER BY nonexcludedsites) AS [Rows],
nonexcludedsites
FROM workflowoutputcadas
)
update CteData
set nonexcludedsites = 1
WHERE [Rows] between 40 and 50答案2：
USE YourDatabase
GO
SELECT ROW_NUMBER()
OVER (ORDER BY nonexcludedsites) AS [Rows],
nonexcludedsites INTO #Temp
FROM workflowoutputcadas
GO
UPDATE workflowoutputcadas
set nonexcludedsites = 1
FROM workflowoutputcadas INNER JOIN #Temp ON #Temp.nonexcludedsites = workflowoutputcadas.nonexcludedsites
WHERE #Temp.[Rows] between 40 and 50
GO
DROP TABLE #Temp我假设非排除性站点是一个可以用于关系的字段
Stack Overflow用户
发布于 2016-02-19 21:11:41
试试这个：
update workflowoutputcadas
set nonexcludedsites = 1
FROM
workflowoutputcadas a --****
LEFT JOIN --****
(SELECT ROW_NUMBER()
OVER (ORDER BY nonexcludedsites) AS Row,
nonexcludedsites
FROM workflowoutputcadas) AS w
on a.nonexcludedsites = w.nonexcludedsites --****
WHERE Row between 40 and 50我已经添加了注释掉的星号来标记我已经添加到原始查询中的行。
或者，您可以使用两个子查询，但它开始变得混乱：
update workflowoutputcadas
set nonexcludedsites = 1
where nonexcludedsites in
(Select nonexcludedsites from
(SELECT ROW_NUMBER()
OVER (ORDER BY nonexcludedsites) AS Row,
nonexcludedsites
FROM workflowoutputcadas) AS w
WHERE Row between 40 and 50)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/35490170
复制
腾讯云开发者
