Pass Through UPDATE Query

[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?

Created directly 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...

However, the same structure does not work as a SQL pass through query and i don't really understand where to declare table2 (people) properly.

At the moment i can only get it to work declaring people in a sub query but i believe it needs to be declared somewhere in the main query, I just don't know where. I have tried many different combinations using the FROM clause, but whenever i use this i am seeing an error.

Most recent attempt....

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

Returns error: 'missing expression at FROM'.

Any help on this would be great.

[NeoPa]

Let me deal with points one at a time.

1. I've deleted your other thread where you posted in error so needn't worry about that.
2. You've obviously made efforts to show the code clearly. In future, simply use the [ CODE ] tags provided (click on the hash (#) button).
3. Your first SQL string has TWO WHERE keywords. This will confuse the SQL engine and may explain your problems.

try fixing that and let us know how you get on.

[Lewe22]

Thanks i'm still a newbie as you can see! Was wondering how everyone did that.
Please ignore the second WHERE, obviously a pasting error on my part as it does not exist in my code.

[NeoPa]

Scratch the comment (#3) about the WHERE clauses. I missed the parentheses.
However, it seems (from the help) that the AS Alias predicates are not supported in the Update table.

[Lewe22]

Understood about the WHERE, but a little confused after that...

[NeoPa]

I believe you should also omit the table reference within the SET clause anyway.

[NeoPa]

Understood about the WHERE, but a little confused after that...

OK.
Let me sort out some example SQL for you then.

[Lewe22]

OK.
Let me sort out some example SQL for you then.

That would be great!

Just to clarify, my code is currently...

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

[NeoPa]

Did some digging & found that Help not too reliable here.
Try this out anyway :

UPDATE (Learner_ilr AS L INNER JOIN People AS P
        ON L.person_code = P.person_code)
SET L.prior_attain_level = P.prior_attain_level
WHERE L.prior_attain_level IS NULL
  AND P.prior_attain_level IS NOT NULL
  AND L.funding_year = 13

[Lewe22]

Returns the error 'ORA-00928: missing SELECT keyword' just after the open bracket...

[NeoPa]

Returns the error 'ORA-00928: missing SELECT keyword' just after the open bracket...

That's confusing.
I modelled it on a working update query :

UPDATE (tblRouteDrops LEFT JOIN MAST_ROUTE ON (tblRouteDrops.BRANCH_CODE = MAST_ROUTE.BRANCH_CODE)
   AND (tblRouteDrops.ROUTE_CODE = MAST_ROUTE.ROUTE_CODE))
LEFT JOIN MAST_DELADD ON (tblRouteDrops.CUST_ACCOUNT_NBR = MAST_DELADD.CUST_ACCOUNT_NBR)
   AND (tblRouteDrops.CUST_DEPOT_NUMBER = MAST_DELADD.CUST_DEPOT_NBR)
   AND (tblRouteDrops.DELIVERY_ADDR_NBR = MAST_DELADD.DELIVERY_ADDR_NBR)
SET tblRouteDrops.NORMAL_DAY_FOR_THE_DELIVERY = [MAST_ROUTE].[NORMAL_DAY_FOR_THE_DELIVERY],
    tblRouteDrops.DESCRIPTION_OF_ROUTE = [MAST_ROUTE].[DESCRIPTION_OF_ROUTE],
    tblRouteDrops.NAME = [MAST_DELADD].[NAME],
    tblRouteDrops.ADDR_1 = [MAST_DELADD].[ADDR_1],
    tblRouteDrops.ADDR_2 = [MAST_DELADD].[ADDR_2],
    tblRouteDrops.ADDR_3 = [MAST_DELADD].[ADDR_3],
    tblRouteDrops.ADDR_4 = [MAST_DELADD].[ADDR_4]

I cannot see why it would be expecting a SELECT at that point. I know parentheses can introduce a sub-query which starts with a SELECT - but why would it expect that here?
I will need to have another look later (lunch finished now I'm afraid :( ).

[NeoPa]

Try out (just in case) :

UPDATE Learner_ilr AS L INNER JOIN People AS P
        ON L.person_code = P.person_code
SET L.prior_attain_level = P.prior_attain_level
WHERE L.prior_attain_level IS NULL
  AND P.prior_attain_level IS NOT NULL
  AND L.funding_year = 13

[Lewe22]

Unfortunately i get error: 'ORA-00971: missing SET keyword' just before the first AS.

It just doesn't seem to like the structure, which is what's been confusing me.

Thanks a lot for taking the time out to help by the way! More luck tomorrow lunch time perhaps! Cheers

[NeoPa]

Lets try out a basic simplification and see if that gets us anywhere :

UPDATE Learner_ilr INNER JOIN People
        ON Learner_ilr.person_code = People.person_code
SET Learner_ilr.prior_attain_level = People.prior_attain_level
WHERE Learner_ilr.prior_attain_level IS NULL
  AND People.prior_attain_level IS NOT NULL
  AND Learner_ilr.funding_year = 13

If that doesn't work we may be into posting the table MetaData and seeing if I can reproduce your situation.

[Lewe22]

No joy there either, still 'missing SET keyword' (before INNER JOIN).

OK....a bit of background.....

The two tables in question, people and learner_ilr, are both stored in a SQL database on a server. In both tables 'prior_attain_level' has a data type of text and 'person_code' a data type of number.

My objective is to import the 'prior_attain_level' from 'people' to 'learner_ilr' where 'learner_ilr.prior_attain_level' IS NULL for a particular 'funding_year'. It is also important that 'people.prior_attain_level' IS NOT NULL so as not to import a NULL value over an existing NULL value.

I have successfully written a section of SQL code which i am inserting into VB which is behind a command button from an Access form.

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

This manages to perform the update ok, however as we know line 5 (in bold) appears to be being ignored. Ordinarily due to the nature of it being about NULL values this wouldn't bother me, however in VB i am also performing a 'Records Affected' procedure (to store in a seperate table along with other details about the update) and do not want to count a NULL prior_attain_level in 'people' replacing a NULL prior_attain_level in 'learner_ilr'.

[NeoPa]

You are doing this in Access I presume.
I knocked up a quick test rig and I had no problems.
Give me a few minutes and I'll post what I have.

[Lewe22]

Thanks. Yeah, i'm actually testing the code in SQL Tools 1.42 but essentially it is to be inserted into an access pass through query.

[NeoPa]

tblPeople
PersonID Name Attained
   1     Alpha
   2     Bravo
   3     Charlie  3
   4     Delta
   5     Echo
   6     Foxtrot  6

tblPeople
PersonID Name Attained
   1     Alpha
   2     Bravo    2
   3     Charlie
   4     Delta
   5     Echo     5
   6     Foxtrot

The SQL I ran was :

UPDATE tblLearner INNER JOIN tblPeople ON tblLearner.PersonID = tblPeople.PersonID
SET tblLearner.Attained = [tblPeople].[Attained]
WHERE ((tblLearner.Attained Is Null) AND (tblPeople.Attained Is Not Null))

I ended up (after it reporting two lines updated) with

tblPeople
PersonID Name Attained
   1     Alpha
   2     Bravo    2
   3     Charlie  3
   4     Delta
   5     Echo     5
   6     Foxtrot  6

[NeoPa]

Thanks. Yeah, i'm actually testing the code in SQL Tools 1.42 but essentially it is to be inserted into an access pass through query.

Scream!!!
That's why the Access SQL won't work then :(

[Lewe22]

Hang on...i'm sure i said that in my first post. Appologies

I appreciate that the Access SQL will not work, yet am still none the wiser as to what will !!!

[NeoPa]

Let me explain a little :
A Pass-Thru query uses the SQL of the server and is not processed by Access at all.
In this case that would be T-SQL (or Transact-SQL) and, although I could dig that up somewhere, I suspect your best bet is to ask this question of the SQL Server team instead, as they deal with this every day so are not as rusty as I am.

Let me know if you would like this whole thread moved across to the SQL Server forum (perhaps after this experience you'll be one of the few members who understand the importance of making the question clear up front :D).

Good luck anyway - it's been fun really.

[NeoPa]

Hang on...i'm sure i said that in my first post. Appologies

I appreciate that the Access SQL will not work, yet am still none the wiser as to what will !!!

I beg your pardon. So you did. Perhaps I'll now be one of the few experts who appreciate the importance of reading the whole post thoroughly - especially before criticising the OP :D

[Lewe22]

Ha! Thanks a lot for your help. Yeah, please move the thread accross although i have my reservations about it being addressed again as it already has 22 replies!!

Till next time...

[NeoPa]

In the circumstances I can Report your post which will bring it to the attention of a moderator or two in that forum. I will request that they give it attention in spite of it looking done and dusted.
It is considered acceptable (btw) to bump a thread after over 24 hours of inactivity. Let's see how we go.

[Lewe22]

Great stuff!! .

[NeoPa]

Actually I found a better way - I'll post a thread in their Experts lounge (Now I just have to find a way to navigate there - watch this space).

[Lewe22]

You're a star. Can you please keep me posted then.

[NeoPa]

No problem.
The way it will work is that all the experts (not as busy nor as well populated a team as Access but some of them I know, & they are quality people) will get an email notifying them of the new thread which has a link into this one. As they find it they will come over to look and help if they can.

I doubt this will happen (although the Help thread was a new one for them), but if you see no response in here within 24 hours please post again in here (bump) and I (who am already registered in here) will see that and consider getting in touch directly with some of the moderators in the team. As I say though, I very much doubt it will come to that. Don't worry.

[Lewe22]

Just PM'd you NeoPa. As yet no response from any other experts. Am hoping a small amount of chasing could be done for me. Many thanks!!

[NeoPa]

I don't want to do anything like chasing atm as we've just been through a weekend (seems like a long time but not really) but I'll check with a couple of people to see if they've noticed this yet.

[iburyak]

Post this thread on Oracle forum.

Thank you.

[NeoPa]

Post this thread on Oracle forum.

Thank you.

You're right Iburyak.
Unfortunately I only had what the OP posted and you'll see he only refers to the back end as a SQL server - even after the whole issue came up. After the issue was discussed I assumed (reasonably I think) that if he didn't mean Microsoft SQL Server he would have mentioned which he was using.
I see now (from the error messages) that the back end must be an Oracle server (from the prefix "ORA"). I will have to go through the whole process again for the Oracle forum it seems. Nice.
Oh well, at least it will mean they get a "Help" thread too.

[Motoma]

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?

I am assuming you are running this in Query Analyzer.
If you highlight the inner SELECT statement (everything inside the parenthesis), and hit F5 (run query), do you get the result set you are expecting?

[iburyak]

Different servers treat nulls differently it depends on server settings.
On SQL server a bullet proof approach is:

[PHP]isnull(column_name, '') <> ''[/PHP]

For Oracle I don't know, sorry.

[NeoPa]

I will move this to the Oracle forum but tight for time ATM.
You guys can relax as this was never a SQL Server question.
Thanks for your answers anyway.

PS It's moved now but I'll get around to setting up all the paraphernalia later.

[r035198x]

Will this work?

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 OR
(l.prior_attain_level IS NULL AND
l.funding_year = 13))

[amitpatel66]

Try below Query:

UPDATE (SELECT person_code, prior_attain_level 
        FROM learner_ilr
        WHERE prior_attain_level IS NULL 
        AND funding_year = 13
        AND    person_code IN (SELECT person_code 
                           FROM people WHERE prior_attain_level IS NOT NULL) e SET e.prior_attain_level = (SELECT prior_attain_level FROM people WHERE person_code = e.person_code)
 
 

[Lewe22]

Result at last!! Thanks amitpatel66!! Cheers everyone, especially NeoPa who went well out of his way to help, much appreciated!!

[NeoPa]

Result at last!! Thanks amitpatel66!! Cheers everyone, especially NeoPa who went well out of his way to help, much appreciated!!

I'm very pleased this has finally been resolved. My personal thanks to all who pitched in and tried to help.

Good luck with your project.