在SQL Update中使用Distinct

Question

这是我需要转换为Update的Select查询语句：

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%'

不确定如何应用Distinct

- Earl

---

“update distinct” 是什么意思？你能展示一下更新前后的数据吗？ - Abe Miessler

在更新数据之前使用SELECT查看数据的工作做得很好。但我不知道为什么你想使用DISTINCT投影。所有匹配的行都应该被更新，使用DISTINCT似乎没有逻辑上的意义。 - Yuck

是的，SQL Server。是的，应该更新所有匹配的行。只需将位从1设置为0即可。也许我不需要DISTINCT。 - Earl

Answer 1

根据您的评论，所有匹配的行都应该被更新，您不需要使用distinct。只需执行以下操作：

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%'

你所看到的所有内容都将被更新。

- Abe Miessler

Answer 2

选项1：使用临时表来保存您的初始查询结果，然后根据查询结果进行更新。 选项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%'

- Jeremy F.

Answer 3

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%'
                                     )
                  );

- onedaywhen

Answer 4

你可以尝试以下方法。

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
) 

- alexm