locked
Syntax errors in update query with inner joins and sub query. RRS feed

  • General discussion

  • 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.....

    Wednesday, September 17, 2014 7:02 PM

All replies

  • 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.....

    sp_CFQ_Coord_Corrections is a table and not a stored procedure.
    Wednesday, September 17, 2014 7:03 PM
  • 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.....

    sp_CFQ_Coord_Corrections is a table and not a stored procedure.

    are these both tables "sp_CFQ_Coord_Corrections" and "CFQ_Coord_Corrections" different ??
    Wednesday, September 17, 2014 7:13 PM
  • try the below query...it should help,..

    UPDATE sp_CFQ_Coord_Corrections 
    INNER JOIN (select CFQ_Referrals.RecID from 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));

    Wednesday, September 17, 2014 7:24 PM
  • Yes, they are different.
    Wednesday, September 17, 2014 7:42 PM
  • let me check....
    Wednesday, September 17, 2014 7:42 PM
  • When I try the above query, I see the below errors:

    Msg 156, Level 15, State 1, Line 2
    Incorrect syntax near the keyword 'INNER'.
    Msg 156, Level 15, State 1, Line 4
    Incorrect syntax near the keyword 'ON'.
    Msg 102, Level 15, State 1, Line 9
    Incorrect syntax near 'CFQ_Coord_Corrections'.

    Wednesday, September 17, 2014 7:43 PM
  • When I try the above query, I see the below errors:

    Msg 156, Level 15, State 1, Line 2
    Incorrect syntax near the keyword 'INNER'.
    Msg 156, Level 15, State 1, Line 4
    Incorrect syntax near the keyword 'ON'.
    Msg 102, Level 15, State 1, Line 9
    Incorrect syntax near 'CFQ_Coord_Corrections'.

    try this ...forgot to add alias name

    UPDATE sp_CFQ_Coord_Corrections 
    INNER JOIN (select CFQ_Referrals.RecID, CFQ_Coord_Corrections.MatchFound, CFQ_Coord_Corrections.RecTblID, [CFQ_Referrals].[RecTblID] As Ref_TblID  from CFQ_Coord_Corrections 
    INNER JOIN CFQ_Referrals ON CFQ_Coord_Corrections.CorrID = CFQ_Referrals.RecID) res
    ON sp_CFQ_Coord_Corrections.ID = res.RecID 
    SET res.MatchFound = 1, 
    res.RecTblID = res.Ref_TblID
    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));

    Wednesday, September 17, 2014 8:38 PM
  • I got the solution. Understood that we cannot update two tables in a single update query. Gotta have 2 separate queries.
    Wednesday, September 17, 2014 9:18 PM