That doesn't work, error 'missing expression' at FROM.
I've spent a long time trying to figure this out and in particular it is the position of the FROM that i can't get to work.
Created in Access the structure of the query works like:
UPDATE table1 INNER JOIN table2
ON table1.field1 = table2.field2
SET table1.fieldx = table2.fieldn
WHERE conditions...
The same structure does not work as a SQL pass through query though and i don't really understand where to declare table2 (people) peoperly.
At the moment i am declaring it in the sub query but realise it needs to be declared somewhere in the main update. HELP!!
UPDATE learner_ilr l
SET l.prior_attain_level =
(SELECT p.prior_attain_level
FROM people p
WHERE p.person_code = l.person_code
AND p.prior_attain_level IS NOT NULL)
WHERE
l.prior_attain_level IS NULL AND
l.funding_year = 13