在 SQL 更新中使用 Distinct
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7163271/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using Distinct in SQL Update
提问by Earl
Here is the Select I need to convert to an Update:
这是我需要转换为更新的选择：
SELECT DISTINCT f.SectionID, f.Name, v.Enabled
FROM SETTING_VALUE v
INNER JOIN SETTING s ON v.SettingID = s.SettingID
INNER JOIN LU_FIELD f ON f.FieldID = s.FieldID
WHERE v.DisplayValue LIKE '%Miami%'
AND f.ControlName LIKE '%City%'
My attempt:
我的尝试：
UPDATE SETTING_VALUE
SET Enabled = 0
FROM SETTING_VALUE v
INNER JOIN SETTING s ON v.SettingID = s.SettingID
INNER JOIN LU_FIELD f ON f.FieldID = s.FieldID
WHERE v.DisplayValue LIKE '%Miami%'
AND f.ControlName LIKE '%City%'
Not sure how to apply Distinct
不确定如何应用 Distinct
回答by Abe Miessler
Based on your comment, all of the matching rows should be updatedyou don't need distinct. Just execute this:
根据您的评论，all of the matching rows should be updated您不需要不同。只需执行这个：
UPDATE SETTING_VALUE
SET Enabled = 0
FROM SETTING_VALUE v
INNER JOIN SETTING s ON v.SettingID = s.SettingID
INNER JOIN LU_FIELD f ON f.FieldID = s.FieldID
WHERE v.DisplayValue LIKE '%Miami%'
AND f.ControlName LIKE '%City%'
Everything that you saw in your select will be updated.
您在选择中看到的所有内容都将更新。
回答by Jeremy F.
Option 1: Use a temporary table to hold your initial query results, then update based on the query results.
选项1：使用临时表来保存您的初始查询结果，然后根据查询结果进行更新。
Option 2:
选项 2：
Insert into SETTING_VALUE
(SectionID, Name, Enabled)
SELECT DISTINCT f.SectionID, f.Name, v.Enabled
FROM SETTING_VALUE v
INNER JOIN SETTING s ON v.SettingID = s.SettingID
INNER JOIN LU_FIELD f ON f.FieldID = s.FieldID
WHERE v.DisplayValue LIKE '%Miami%'
AND f.ControlName LIKE '%City%'
回答by onedaywhen
UPDATE SETTING_VALUE
SET Enabled = 0
WHERE DisplayValue LIKE '%Miami%'
AND EXISTS (
SELECT *
FROM SETTING s
WHERE SETTING_VALUE.SettingID = s.SettingID
AND EXISTS (
SELECT *
FROM LU_FIELD f
WHERE f.FieldID = s.FieldID
AND f.ControlName LIKE '%City%'
)
);
回答by alexm
You can try the following
您可以尝试以下操作
UPDATE v
SET Enabled = 0
FROM SETTING_VALUE v
WHERE EXISTS(
select * from SETTING s
INNER JOIN LU_FIELD f ON f.FieldID = s.FieldID
WHERE v.DisplayValue LIKE '%Miami%'
AND f.ControlName LIKE '%City%'
AND v.SettingID = s.SettingID
)
