UPDATE 脚本不起作用 - 0 行受影响
我有一个 SQL Server 数据库，我正在尝试在其中一个表上运行更新脚本，但它一直显示“0 行受影响”。
如果我运行以下脚本，它会显示“33 Rows Affected”，
UPDATE [StoreTestDB].[dbo].[ProductVariant]
SET [IsDefault] = 0,
[Published] = 0
WHERE ProductID = 19
但如果我运行以下脚本，它会显示“0 Rows Affected”：
UPDATE [StoreTestDB].[dbo].[ProductVariant]
SET [IsDefault] = 0,
[Published] = 0
WHERE ProductID = 19
AND ProductID = 20
AND ProductID = 23
AND ProductID = 24
AND ProductID = 25
AND ProductID = 27
AND ProductID = 28
AND ProductID = 29
AND ProductID = 30
AND ProductID = 31
AND ProductID = 32
AND ProductID = 33
AND ProductID = 54
AND ProductID = 55
AND ProductID = 56
AND ProductID = 57
AND ProductID = 58
AND ProductID = 64
AND ProductID = 71
AND ProductID = 72
AND ProductID = 73
AND ProductID = 74
AND ProductID = 75
AND ProductID = 77
AND ProductID = 105
AND ProductID = 109
AND ProductID = 152
AND ProductID = 157
AND ProductID = 158
AND ProductID = 162
AND ProductID = 164
AND ProductID = 165
AND ProductID = 167
AND ProductID = 169
AND ProductID = 170
AND ProductID = 173
AND ProductID = 174
我在这里做错了什么？
我确信 ProductID 的 IsDefault = 1 和 Published = 1，这就是我想运行该脚本的原因。
有什么想法吗？
I have an SQL Server database and i am trying to run an update script on one of the tables but it keeps saying "0 Rows Affected".
If i run the following script it says "33 Rows Affected"
UPDATE [StoreTestDB].[dbo].[ProductVariant]
SET [IsDefault] = 0,
[Published] = 0
WHERE ProductID = 19
But if i run the following script it says "0 Rows Affected":
UPDATE [StoreTestDB].[dbo].[ProductVariant]
SET [IsDefault] = 0,
[Published] = 0
WHERE ProductID = 19
AND ProductID = 20
AND ProductID = 23
AND ProductID = 24
AND ProductID = 25
AND ProductID = 27
AND ProductID = 28
AND ProductID = 29
AND ProductID = 30
AND ProductID = 31
AND ProductID = 32
AND ProductID = 33
AND ProductID = 54
AND ProductID = 55
AND ProductID = 56
AND ProductID = 57
AND ProductID = 58
AND ProductID = 64
AND ProductID = 71
AND ProductID = 72
AND ProductID = 73
AND ProductID = 74
AND ProductID = 75
AND ProductID = 77
AND ProductID = 105
AND ProductID = 109
AND ProductID = 152
AND ProductID = 157
AND ProductID = 158
AND ProductID = 162
AND ProductID = 164
AND ProductID = 165
AND ProductID = 167
AND ProductID = 169
AND ProductID = 170
AND ProductID = 173
AND ProductID = 174
What am i doing wrong here?
I am certain the productIDs have a IsDefault = 1 and Published = 1, which is why i want to run the script.
Any ideas?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
您的错误是使用 AND。
想一想：一个产品不能同时有不同的ID；您想要的是更新不同的产品，每个 id 一个。
所以你必须使用 OR：
一个紧凑的语法是：
Your error is the use of AND.
Think about it: a product cannot have at the same time different IDs; what you want is update different products, one for each id.
So you have to use OR:
A compact syntax is:
您的 WHERE 子句有错误。您不想更新 ProductID 为所有这些 id 的行（因为每行只能有一个 ProductID，因此您的 WHERE 语句永远不会为 true），而是一一更新。您应该使用 IN 代替，如下所示：
这将单独更新所有行。
Your WHERE clause is faulty. You don't want to update the row where the productID is ALL of those id's (since there can only be one ProductID per row, so your WHERE statement will never be true), but one by one. You should use IN instead, like so:
That will update all of the rows individually.
用 OR 代替。您是说 ProductID 应该同时为 19、20...。
User OR instead. You're saying that ProductID should be 19, 20, ... at the same time.
好吧，我认为问题是您将所有 id 组合在一起，即产品的 id 为 162 或 164，但不能同时为两者。
尝试使用 IN 语句，即：
WHERE ProductID IN (19,20,21,22 ETC)
应该有更多的运气！
Well i would think the problem is you are and-ing all your id's together IE a product has an id of 162 OR 164 but not both.
Try your statement using an IN, ie:
WHERE ProductID IN (19,20,21,22 ETC)
Should have more luck!
两个选项：
AND更改为ORIN子句Two options:
ANDtoORINclause