running an update with outter join error RRS feed

  • Question

  • This works fine in SQL, but when trying in access I get error.

    Syntax error (missing operator) in query expression......

    Below is my script

    update j

    set community = j.[location]

    from [dbo].[cfeb7] j

    left outer join directory_community d1 on j.[Community] = d1.community

    left outer join directory_community d2 on j.[Location] = d2.community

    where d1.community is null and d2.community is not null

    Friday, February 15, 2019 4:57 PM

All replies

  • In Ace-SQL we need parentheses when more than one JOIN is used. And the table to update must be named first. Also the name of the cfeb7 table is invalid in Access.

    So the question is what are you trying exactly?

    In Access it is:

    UPDATE directory_community AS d1 
    		cfeb7 AS j 
    			INNER JOIN directory_community AS d2 
    				ON j.location = d2.community
    	) ON d1.community = j.community 
    SET j.community = [j].[locaton]
    WHERE (((d1.community) Is Null));

    Saturday, February 16, 2019 4:03 PM
  • Why is the table name cfeb7 considered invalid?

    I am aware that parentheses are needed, but not sure where and how based on my query.

    I am wanting to do a match against the cfeb7 table and look at the location column.

    For every record do a compare and confirm that value in that record exists in directory_community table (community) column, if it does, I want to update the cfeb7 location table with the community value.

    Tuesday, February 19, 2019 1:18 PM
  • Cause it an invalid object name in Access:

    Tuesday, February 19, 2019 1:45 PM
  • OK, truth be told the name i have was not exactly that so i didn't know that.

    Still dont know what the issue is with that name.

    The actually table name is Jobs.

    Tuesday, February 19, 2019 1:48 PM
  • Well, what's wrong in using the designer?
    Tuesday, February 19, 2019 2:19 PM
  • I am not familiar with using the desinger for doing outter joins, well any joins for that matter.

    I come from the SQL query writing world.

    Tuesday, February 19, 2019 2:20 PM
  • Any thoughts on suggestions on how I can get this going?

    Are you aware of parentheses needed?

    Tuesday, February 19, 2019 7:31 PM