带有JOIN查询的简单UPDATE SET问题。我该如何解决这个问题？ [英] Problem with simple UPDATE SET with JOIN query. How can I resolve this?

问题描述

你好，我收到一个错误：

消息102，等级15，状态1，行28'''附近的语法不正确。



这似乎发生在声明：SET fa.Focus_FarmExceptional ='Y'



我似乎无法看到它。我一直在看这个并且对我而言，看起来它应该工作得很好但不是。非常感谢任何帮助。

Hello, I am getting an error:
Msg 102, Level 15, State 1, Line 28 Incorrect syntax near '.'.

This seems to be occurring at statement: SET fa.Focus_FarmExceptional = 'Y'

I can't seem to see it. I've been looking at this for a while and to me, it appears like it should work just fine but is not. Any help greatly appreciated.

DECLARE @FarmList NVARCHAR(MAX)
DECLARE @CommercialList NVARCHAR(MAX)
DECLARE @PersonalList NVARCHAR(MAX)
DECLARE @tbl_Farm TABLE(ID VARCHAR(8))
DECLARE @tbl_Personal TABLE(ID VARCHAR(8))
DECLARE @tbl_Commercial TABLE(ID VARCHAR(8))

BEGIN
UPDATE  [DCT-Dev_ExampleData].[dbo].[tbl_FocusAgents] 
--reset/default all rows 
SET Focus_FarmExceptional = 'N',
Focus_CommercialExceptional = 'N',
Focus_PersonalExceptional = 'N'
END
--FARM


--populate Farm list for tables
SET @FarmList = '0008144,0007844,0007854,000321,000123'
INSERT INTO @tbl_Farm (ID) 
SELECT * FROM dbo.Split(@FarmList,',')
SELECT * FROM @tbl_Farm

BEGIN
UPDATE  [DCT-Dev_ExampleData].[dbo].[tbl_FocusAgents] 
--update Farm
SELECT * FROM @tbl_Farm 
SELECT * from  dbo.tbl_FocusAgents fa
INNER JOIN @tbl_Farm f on f.ID = fa.Focus_FocusAgentCd
SET fa.Focus_FarmExceptional = 'Y'
WHERE fa.Focus_FocusAgentCd IN (SELECT ID FROM @tbl_Farm)
END

推荐答案

您的更新语句中包含的内容远远超出实际需要。双选（如果它们甚至支持）并且where子句是不必要的。这应该做你想要完成的事情：

You have far more in your update statement than you actually need. The double selects (if they are even supported) and the where clause are unnecessary. This should do what you're trying to accomplish:

UPDATE fa
SET fa.Focus_FarmExceptional = 'Y'
FROM        dbo.tbl_FocusAgents fa
INNER JOIN  @tbl_Farm f ON f.ID = fa.Focus_FocusAgentCd

我们在这里所做的就是构建一个from子句，只选择我们想要更新的记录，然后更新别名表'fa'而不是从中选择。一旦你理解了如何做就相当简单。

All we're doing here is building a from clause to select only the records we want to update, then updating the aliased table 'fa' rather than selecting from it. Rather simple once you understand how to do it.

不确定你在这里想做什么

Not sure what you are trying to do here

SET @FarmList = '0008144,0007844,0007854,000321,000123';
INSERT INTO @tbl_Farm (ID)
SELECT * FROM dbo.Split(@FarmList, ',')
SELECT * FROM @tbl_Farm

但是尝试将记录插入到获取数据的同一个表中是一个坏主意。



另外以下代码不是运行更新查询的标准方式

but it is a bad idea to try and do a insert records into the same table you getting the data from.

Also the following code is not the standard way run an update query

UPDATE  [DCT-Dev_ExampleData].[dbo].[tbl_FocusAgents] 
--update Farm
SELECT * FROM @tbl_Farm 
SELECT * from  dbo.tbl_FocusAgents fa
INNER JOIN @tbl_Farm f on f.ID = fa.Focus_FocusAgentCd
SET fa.Focus_FarmExceptional = 'Y'
WHERE fa.Focus_FocusAgentCd IN (SELECT ID FROM @tbl_Farm)

它应该更像是

it should be somthing more like

UPDATE [tbl_FocusAgents]
SET fa.Focus_FarmExceptional = 'Y'
WHERE fa.Focus_FocusAgentCd IN (SELECT ID FROM @tbl_Farm)

并在where子句中添加您需要的任何其他select语句。

and add any other select statements you need as part of the where clause.

这篇关于带有JOIN查询的简单UPDATE SET问题。我该如何解决这个问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！