Syntax errors in update query with inner joins

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Syntax errors in update query with inner joins

Author

Topic

SQL_SSIS_Dev
Starting Member

10 Posts

Posted - 2014-09-17 : 16:35:02

Below is the query:

UPDATE sp_CFQ_Coord_Corrections
INNER JOIN (CFQ_Coord_Corrections
INNER JOIN CFQ_Referrals ON CFQ_Coord_Corrections.CorrID = CFQ_Referrals.RecID)
ON sp_CFQ_Coord_Corrections.ID = CFQ_Referrals.RecID
SET CFQ_Coord_Corrections.MatchFound = 1,
CFQ_Coord_Corrections.RecTblID = [CFQ_Referrals].[RecTblID],
sp_CFQ_Coord_Corrections.MatchFound = 1

WHERE (((CFQ_Coord_Corrections.MatchFound)=0)
AND ((sp_CFQ_Coord_Corrections.MatchFound)=0)
AND ((CFQ_Coord_Corrections.RecImported)=1)
AND ((CFQ_Referrals.RecFileName)='COORDCORR_SPOINT')
AND ((CFQ_Referrals.RecCombKey)='No.Match')
AND ((sp_CFQ_Coord_Corrections.RecImported)=1));

Error messages seen when executed:

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'INNER'.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near 'CFQ_Coord_Corrections'.

Please help.....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-17 : 16:44:46

There are many issues with your query. I started to fix it but then stopped when I noticed you are trying to update more than one table. You can't do that. You'll need separate update statements.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-17 : 16:45:39

Here's the first one:

UPDATE sp_CFQ_Coord_Corrections
SET MatchFound = 1
FROM sp_CFQ_Coord_Corrections
INNER JOIN CFQ_Referrals ON sp_CFQ_Coord_Corrections.ID = CFQ_Referrals.RecID
INNER JOIN CFQ_Coord_Corrections ON CFQ_Coord_Corrections.CorrID = CFQ_Referrals.RecID
WHERE (((CFQ_Coord_Corrections.MatchFound)=0)
AND ((sp_CFQ_Coord_Corrections.MatchFound)=0)
AND ((CFQ_Coord_Corrections.RecImported)=1)
AND ((CFQ_Referrals.RecFileName)='COORDCORR_SPOINT')
AND ((CFQ_Referrals.RecCombKey)='No.Match')
AND ((sp_CFQ_Coord_Corrections.RecImported)=1));

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-17 : 16:47:15

And I think this is what you need for the second:

UPDATE CFQ_Coord_Corrections
SET MatchFound = 1,
RecTblID = [CFQ_Referrals].[RecTblID]
FROM sp_CFQ_Coord_Corrections
INNER JOIN CFQ_Referrals ON sp_CFQ_Coord_Corrections.ID = CFQ_Referrals.RecID
INNER JOIN CFQ_Coord_Corrections ON CFQ_Coord_Corrections.CorrID = CFQ_Referrals.RecID
WHERE (((CFQ_Coord_Corrections.MatchFound)=0)
AND ((sp_CFQ_Coord_Corrections.MatchFound)=0)
AND ((CFQ_Coord_Corrections.RecImported)=1)
AND ((CFQ_Referrals.RecFileName)='COORDCORR_SPOINT')
AND ((CFQ_Referrals.RecCombKey)='No.Match')
AND ((sp_CFQ_Coord_Corrections.RecImported)=1));

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

SQL_SSIS_Dev
Starting Member

10 Posts

Posted - 2014-09-17 : 16:50:57

checking.....

SQL_SSIS_Dev
Starting Member

10 Posts

Posted - 2014-09-17 : 17:15:29

Fixed. Thank you so much Sir.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-17 : 17:56:11

No problem. I'm female, by the way.