469,268 Members | 942 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,268 developers. It's quick & easy.

Pass Through UPDATE Query

94
When performing the following update:

Expand|Select|Wrap|Line Numbers
  1. UPDATE learner_ilr l
  2. SET l.prior_attain_level = 
  3. (SELECT p.prior_attain_level
  4. FROM people p
  5. WHERE p.person_code = l.person_code
  6. AND p.prior_attain_level IS NOT NULL)
  7. WHERE
  8. l.prior_attain_level IS NULL AND
  9. 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:

Expand|Select|Wrap|Line Numbers
  1. UPDATE table1 INNER JOIN table2 
  2. ON table1.field1 = table2.field2
  3. SET table1.fieldx = table2.fieldn 
  4. 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....

Expand|Select|Wrap|Line Numbers
  1. UPDATE learner_ilr l
  2. SET l.prior_attain_level = p.prior_attain_level
  3. FROM people p INNER JOIN  p.person_code = l.person_code
  4. WHERE p.prior_attain_level IS NOT NULL
  5. AND l.prior_attain_level IS NULL 
  6. AND l.funding_year = 13
Returns error: 'missing expression at FROM'.

Any help on this would be great.
Oct 4 '07 #1
38 5004
NeoPa
32,171 Expert Mod 16PB
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.
Oct 4 '07 #2
Lewe22
94
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.
Oct 4 '07 #3
NeoPa
32,171 Expert Mod 16PB
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.
Oct 4 '07 #4
Lewe22
94
Understood about the WHERE, but a little confused after that...
Oct 4 '07 #5
NeoPa
32,171 Expert Mod 16PB
I believe you should also omit the table reference within the SET clause anyway.
Oct 4 '07 #6
NeoPa
32,171 Expert Mod 16PB
Understood about the WHERE, but a little confused after that...
OK.
Let me sort out some example SQL for you then.
Oct 4 '07 #7
Lewe22
94
OK.
Let me sort out some example SQL for you then.
That would be great!

Just to clarify, my code is currently...

Expand|Select|Wrap|Line Numbers
  1. UPDATE learner_ilr l
  2. SET l.prior_attain_level = (SELECT p.prior_attain_level
  3.                             FROM people p
  4.                             WHERE p.person_code = l.person_code
  5.                             AND p.prior_attain_level IS NOT NULL)
  6. WHERE
  7.   l.prior_attain_level IS NULL AND
  8.   l.funding_year = 13
Oct 4 '07 #8
NeoPa
32,171 Expert Mod 16PB
Did some digging & found that Help not too reliable here.
Try this out anyway :
Expand|Select|Wrap|Line Numbers
  1. UPDATE (Learner_ilr AS L INNER JOIN People AS P
  2.         ON L.person_code = P.person_code)
  3. SET L.prior_attain_level = P.prior_attain_level
  4. WHERE L.prior_attain_level IS NULL
  5.   AND P.prior_attain_level IS NOT NULL
  6.   AND L.funding_year = 13
Oct 4 '07 #9
Lewe22
94
Returns the error 'ORA-00928: missing SELECT keyword' just after the open bracket...
Oct 4 '07 #10
NeoPa
32,171 Expert Mod 16PB
Returns the error 'ORA-00928: missing SELECT keyword' just after the open bracket...
That's confusing.
I modelled it on a working update query :
Expand|Select|Wrap|Line Numbers
  1. UPDATE (tblRouteDrops LEFT JOIN MAST_ROUTE ON (tblRouteDrops.BRANCH_CODE = MAST_ROUTE.BRANCH_CODE)
  2.    AND (tblRouteDrops.ROUTE_CODE = MAST_ROUTE.ROUTE_CODE))
  3. LEFT JOIN MAST_DELADD ON (tblRouteDrops.CUST_ACCOUNT_NBR = MAST_DELADD.CUST_ACCOUNT_NBR)
  4.    AND (tblRouteDrops.CUST_DEPOT_NUMBER = MAST_DELADD.CUST_DEPOT_NBR)
  5.    AND (tblRouteDrops.DELIVERY_ADDR_NBR = MAST_DELADD.DELIVERY_ADDR_NBR)
  6. SET tblRouteDrops.NORMAL_DAY_FOR_THE_DELIVERY = [MAST_ROUTE].[NORMAL_DAY_FOR_THE_DELIVERY],
  7.     tblRouteDrops.DESCRIPTION_OF_ROUTE = [MAST_ROUTE].[DESCRIPTION_OF_ROUTE],
  8.     tblRouteDrops.NAME = [MAST_DELADD].[NAME],
  9.     tblRouteDrops.ADDR_1 = [MAST_DELADD].[ADDR_1],
  10.     tblRouteDrops.ADDR_2 = [MAST_DELADD].[ADDR_2],
  11.     tblRouteDrops.ADDR_3 = [MAST_DELADD].[ADDR_3],
  12.     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 :( ).
Oct 4 '07 #11
NeoPa
32,171 Expert Mod 16PB
Try out (just in case) :
Expand|Select|Wrap|Line Numbers
  1. UPDATE Learner_ilr AS L INNER JOIN People AS P
  2.         ON L.person_code = P.person_code
  3. SET L.prior_attain_level = P.prior_attain_level
  4. WHERE L.prior_attain_level IS NULL
  5.   AND P.prior_attain_level IS NOT NULL
  6.   AND L.funding_year = 13
Oct 4 '07 #12
Lewe22
94
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
Oct 4 '07 #13
NeoPa
32,171 Expert Mod 16PB
Lets try out a basic simplification and see if that gets us anywhere :
Expand|Select|Wrap|Line Numbers
  1. UPDATE Learner_ilr INNER JOIN People
  2.         ON Learner_ilr.person_code = People.person_code
  3. SET Learner_ilr.prior_attain_level = People.prior_attain_level
  4. WHERE Learner_ilr.prior_attain_level IS NULL
  5.   AND People.prior_attain_level IS NOT NULL
  6.   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.
Oct 4 '07 #14
Lewe22
94
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.

Expand|Select|Wrap|Line Numbers
  1. UPDATE learner_ilr l
  2. SET l.prior_attain_level = (SELECT p.prior_attain_level
  3.                             FROM people p
  4.                             WHERE p.person_code = l.person_code
  5.                             AND p.prior_attain_level IS NOT NULL)
  6. WHERE
  7.   l.prior_attain_level IS NULL AND
  8.   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'.
Oct 5 '07 #15
NeoPa
32,171 Expert Mod 16PB
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.
Oct 5 '07 #16
Lewe22
94
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.
Oct 5 '07 #17
NeoPa
32,171 Expert Mod 16PB
Expand|Select|Wrap|Line Numbers
  1. tblPeople
  2. PersonID Name Attained
  3.    1     Alpha
  4.    2     Bravo
  5.    3     Charlie  3
  6.    4     Delta
  7.    5     Echo
  8.    6     Foxtrot  6
Expand|Select|Wrap|Line Numbers
  1. tblPeople
  2. PersonID Name Attained
  3.    1     Alpha
  4.    2     Bravo    2
  5.    3     Charlie
  6.    4     Delta
  7.    5     Echo     5
  8.    6     Foxtrot
The SQL I ran was :
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblLearner INNER JOIN tblPeople ON tblLearner.PersonID = tblPeople.PersonID
  2. SET tblLearner.Attained = [tblPeople].[Attained]
  3. WHERE ((tblLearner.Attained Is Null) AND (tblPeople.Attained Is Not Null))
I ended up (after it reporting two lines updated) with
Expand|Select|Wrap|Line Numbers
  1. tblPeople
  2. PersonID Name Attained
  3.    1     Alpha
  4.    2     Bravo    2
  5.    3     Charlie  3
  6.    4     Delta
  7.    5     Echo     5
  8.    6     Foxtrot  6
Oct 5 '07 #18
NeoPa
32,171 Expert Mod 16PB
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 :(
Oct 5 '07 #19
Lewe22
94
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 !!!
Oct 5 '07 #20
NeoPa
32,171 Expert Mod 16PB
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.
Oct 5 '07 #21
NeoPa
32,171 Expert Mod 16PB
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
Oct 5 '07 #22
Lewe22
94
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...
Oct 5 '07 #23
NeoPa
32,171 Expert Mod 16PB
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.
Oct 5 '07 #24
Lewe22
94
Great stuff!! .
Oct 5 '07 #25
NeoPa
32,171 Expert Mod 16PB
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).
Oct 5 '07 #26
Lewe22
94
You're a star. Can you please keep me posted then.
Oct 5 '07 #27
NeoPa
32,171 Expert Mod 16PB
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.
Oct 5 '07 #28
Lewe22
94
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!!
Oct 8 '07 #29
NeoPa
32,171 Expert Mod 16PB
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.
Oct 8 '07 #30
iburyak
1,017 Expert 512MB
Post this thread on Oracle forum.

Thank you.
Oct 8 '07 #31
NeoPa
32,171 Expert Mod 16PB
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.
Oct 8 '07 #32
Motoma
3,237 Expert 2GB
When performing the following update:

Expand|Select|Wrap|Line Numbers
  1. UPDATE learner_ilr l
  2. SET l.prior_attain_level = 
  3. (SELECT p.prior_attain_level
  4. FROM people p
  5. WHERE p.person_code = l.person_code
  6. AND p.prior_attain_level IS NOT NULL)
  7. WHERE
  8. l.prior_attain_level IS NULL AND
  9. 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?
Oct 8 '07 #33
iburyak
1,017 Expert 512MB
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.
Oct 8 '07 #34
NeoPa
32,171 Expert Mod 16PB
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.
Oct 8 '07 #35
r035198x
13,262 8TB
Will this work?


Expand|Select|Wrap|Line Numbers
  1. UPDATE learner_ilr l SET l.prior_attain_level = (SELECT p.prior_attain_level FROM people p
  2. WHERE p.person_code = l.person_code
  3. AND (p.prior_attain_level IS NOT NULL OR
  4. (l.prior_attain_level IS NULL AND
  5. l.funding_year = 13))
Oct 8 '07 #36
amitpatel66
2,367 Expert 2GB
Try below Query:

Expand|Select|Wrap|Line Numbers
  1. UPDATE (SELECT person_code, prior_attain_level 
  2.         FROM learner_ilr
  3.         WHERE prior_attain_level IS NULL 
  4.         AND funding_year = 13
  5.         AND    person_code IN (SELECT person_code 
  6.                            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)
  7.  
  8.  
Oct 9 '07 #37
Lewe22
94
Result at last!! Thanks amitpatel66!! Cheers everyone, especially NeoPa who went well out of his way to help, much appreciated!!
Oct 9 '07 #38
NeoPa
32,171 Expert Mod 16PB
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.
Oct 9 '07 #39

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by djharrison | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.