UPDATE - Help!!

[Lewe22]

When performing the following update:

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

the section in bold is being ignored. Is there a logical reason why this might be happening? Have i written the update incorrectly?

[ck9663]

When performing the following update:

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

the section in bold is being ignored. Is there a logical reason why this might be happening? Have i written the update incorrectly?

try:

    UPDATE learner_ilr l
    SET l.prior_attain_level = 
    FROM people p INNER JOIN  p.person_code = l.person_code 
    WHERE
    p.prior_attain_level IS NOT NULL
    AND
    l.prior_attain_level IS NULL AND
    l.funding_year = 13

[Lewe22]

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

[ck9663]

try:

    UPDATE learner_ilr l
    SET l.prior_attain_level = 
    FROM people p INNER JOIN  p.person_code = l.person_code 
    WHERE
    p.prior_attain_level IS NOT NULL
    AND
    l.prior_attain_level IS NULL AND
    l.funding_year = 13

let's do that again....

      UPDATE learner_ilr l
          SET l.prior_attain_level = p.prior_attain_level
          FROM people p INNER JOIN  p.person_code = l.person_code
          WHERE
          p.prior_attain_level IS NOT NULL
          AND
          l.prior_attain_level IS NULL AND
          l.funding_year = 13

--CK

[Lewe22]

I did try that but it still didn't work! Same error message!

[Shashi Sadasivan]

I did try that but it still didn't work! Same error message!

i think what ck9663 meant was
[CODE=sql]UPDATE learner_ilr l
SET l.prior_attain_ level =(select p.prior_attain_ level
FROM people p INNER JOIN p.person_code = l.person_code
WHERE
p.prior_attain_ level IS NOT NULL
AND
l.prior_attain_ level IS NULL AND
l.funding_year = 13)[/CODE]
Please try to correct the code youself.

cheers