I'm not a big SQL guru so I may be completely wrong here, but it looks like you're using an alias "a" without actually defining what "a" is.

It is a pretty complex query you are writing. Are you sure it's working?

 

My Sql skills ain't perfect but do you select a table name 'a' with the select statement?

Using 'a' twice is a bit confusing to me. Are you sure the select statement gives back one result and one result only?

 

You 'Set ParcelID = NewParcelID' but don't set KOAUnitID to anything. Why do you mention it. Don't you need a WHERE statement?

 

update a set ParcelID = NewParcelID, KOAUnitID from 
	(select a.ParcelID,b.ParcelID NewParcelID,a.KOA_UNIT_ID KOAUnitID from rGISStaging].sdbo].ÂKAINGAORALETTABLEUNITSTAGINGNZTM] a left join GISPublish.dbo.Parcel b on a.shape.STIntersects(b.shape) = 1) a 

 

Try removing all the line breaks/carriage returns from the UPDATE statement in FME and making it one long single SQL statement line.

I seem to remember getting a similar issue in a SQL statement executing fine in SQL Server Management Studio but not in SQLCreator was because these end-of-line/carriage return characters can get malformed when copying and pasting the SQL statement text from SQL Server Management Studio to FME an then FME not parsing the SQL statement string properly when it sends the statement to the SQL Server Provider. I didn't investigate much further as to why this happened, but it solved my particular problem.