SQL在SET语句中使用WHEN。 [英] SQL USING WHEN in a SET statment.

问题描述

\]

This makes total sense to me but it fails with an error.  I am not much of a SQL programmer.  I have researched this error but still curious why this will not work


<pre lang="SQL">
				
BEGIN
UPDATE [AdventureWorks].[dbo].[tbl_FocusAgents]
WHEN @agency_lob = 'Farm' THEN SET Focus_FarmFocus = 'Y' 
WHEN @agency_lob = 'Commerical' THEN SET Focuse_CommericalFocus = 'Y'
WHERE Focus_FocusAgentCd= @agency_entire_code
END	

推荐答案

你不能根据条件设置列：语法不允许它。

你需要做什么是两个UPDATES或

You can't set columns or not based on conditions: the syntax doesn't allow for it.
What you will have to do is two UPDATES or

UPDATE [AdventureWorks].[dbo].[tbl_FocusAgents]
   SET Focus_FarmFocus = CASE WHEN @agency_lob = 'Farm' THEN 'Y' ELSE Focus_FarmFocus END,
       Focuse_CommericalFocus = WHEN @agency_lob = 'Commerical' THEN 'Y' ELSE Focuse_CommericalFocus END
WHERE Focus_FocusAgentCd= @agency_entire_code

BEGIN

BEGIN
UPDATE [AdventureWorks].[dbo].[tbl_FocusAgents]
SET Focus_FarmFocus = CASE WHEN   @agency_lob = 'Farm' THEN 'Y' ELSE ''  END,
 Focuse_CommericalFocus = CASE WHEN   @agency_lob = 'Commerical' THEN 'Y' ELSE '' END
WHERE Focus_FocusAgentCd= @agency_entire_code
eND

</pre>

这篇关于SQL在SET语句中使用WHEN。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！