根据条件更新随机行

Question

我有多列和多行的表。
我想根据条件随机更新 50% 的行。 这是我尝试过但没有奏效的方法：

UPDATE dbo.tab1
SET column1 = 'A'
FROM dbo.tab1 as a
INNER JOIN (
SELECT TOP 50 percent *
FROM dbo.tab1
WHERE column2 NOT IN ('B','C','D')
ORDER BY NewID()
) as b 
on a.ID = b.ID

Answer 1

也许，column2包含NULL值。尝试使用以下方法

CREATE TABLE tab1(
ID int,
column1 varchar(1),
column2 varchar(1)
)
INSERT tab1(ID,column1)VALUES
(1,'B'),
(2,'B'),
(3,'B'),
(4,'B'),
(5,'B'),
(6,'B')

UPDATE a
SET column1 = 'A'
FROM tab1 a
INNER JOIN
(
SELECT TOP 50 percent *
FROM tab1
--WHERE column2 NOT IN ('B','C','D')
WHERE ISNULL(column2,'') NOT IN ('B','C','D')
ORDER BY NewID()
) b 
ON a.ID = b.ID

SELECT *
FROM tab1

您也可以尝试使用不带JOIN的查询

UPDATE q
SET column1 = 'A'
FROM
(
SELECT TOP 50 percent *
FROM tab1
--WHERE column2 NOT IN ('B','C','D')
WHERE ISNULL(column2,'') NOT IN ('B','C','D')
ORDER BY NewID()
) q

Answer 2

你可以利用 cte 来做这种事情的简短工作。像这样的东西。

with MyUpdate as
(
SELECT TOP 50 percent *
FROM dbo.tab1
WHERE column2 NOT IN ('B','C','D')
ORDER BY NewID()
)
update MyUpdate
set column1 = 'A'