Problem with simple UPDATE SET with JOIN query. How can I resolve this?

Question

1.00/5 (1 vote)

See more:

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.

SQL

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

Posted 11-Jun-14 5:47am

ChoctawHoke

Add a Solution

Comments

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 11-Jun-14 12:09pm

You are selecting inside the Update statement. I am not sure whether it is supported or not. Research on this.

2 solutions

Solution 2

Not sure what you are trying to do here

SQL

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)

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

Posted 11-Jun-14 8:24am

Member 3505965

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kyle Moyer · Accepted Answer · 2014-06-11T07:09:00

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:

SQL

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

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.