We help IT Professionals succeed at work.

Using subquery in update of SQL?

keynes
keynes asked
on
707 Views
Last Modified: 2012-06-27
I run following statement in STRSQL;

UPDATE IIM                              
SET IPFDV=                              
(SELECT IPLC FROM IIMX WHERE IIMX.IXPROD=IIM.IPROD)  

But it can not work.
Our os400 is v4.2.

Comment
Watch Question

Peter PriceDeveloper

Commented:
Does the select return more than one row if you run it manually?

Commented:
Subqueries can only be used in the WHERE clause of an UPDATE or DELETE clause.
I believe you will need to use the CURSOR method with EXEC SQL statements.
You can also try using ODBC and MS Access.

Author

Commented:
I check the book the SQL/400 ref(verison  4.4), it shows that this statement is correct. I wonder that version 4.2 is not support this statement?
Peter PriceDeveloper

Commented:
Does Syntax check fail or does the Execution Fail? Does the select return more than one row??

Author

Commented:
SYNTAX CHECK FAIL
vjg

Commented:
What message do you get? Is an invalid token identified and is a list of valid tokens supplied? I just ran an almost identical query using STRQM in SQL mode and it worked fine.
Peter PriceDeveloper

Commented:
Ah Think your problem may be in your field names. Looking at your naming conventions I suspect that field IPLC is in file IIM not in IIMX. It would appear that IPLC should read IXPLC!!

UPDATE IIM                              
SET IPFDV=                              
(SELECT IPLC FROM IIMX WHERE IIMX.IXPROD=IIM.IPROD)  

You cannot select a field that is not in the table that is the subject of your From clause

Author

Commented:
No. iplc is in IIMX
Peter PriceDeveloper

Commented:
Please Post Exact Text of Message !!

Commented:
According to the SQL programmers guide,  A subquery can only be used in the "WHERE" clause on an UPDATE or DELETE SQL statement.  MFalcon's comment is correct.
vjg

Commented:
Jsuper. I just ran an identical query (except for the file/field names) here. The book is incorrect or you are on a different release.

When my subquery produced a single row, the update occurred. When it produced multiple rows, a run time message was issued indicating a multi-row result set was generated.

Until keynes bothers to put up the exact error messages and the file definitions, we won't be able to answer his/her questions.
vjg

Commented:
Do you have the latest Database Fix Pack installed. These are NOT distributed with Cume tapes.

Author

Commented:
error message:

Token IPLC was not valid. Valid tokens: + ) -.
vjg

Commented:
Have you tried this in STRQM? It worked as described using that utility.

Have you verified library list?

Are you willing to post the file field descriptions (DSPFFD) of the two files?

Does the Select work by itself with the Where clause removed?

I thought it might be a failure to use a correlation name. Surprisingly, it worked in QM without providing explicit correlation names... the file names were enough. Still, I'd consider trying it with correlation names. I didn't think it would allow the use of a query field in a subquery if its file didn't have a correlation name.

Have installed the latest PTFs and the latest Database Fix pack? We had an access path that wouldn't update its index and it took an IBM engineer about 10 seconds to diagnose the problem over the phone... we'd never heard of database fix packs.

Since you're getting an "invalid token" message rather than a "field not in file" message, I'd suspect a PTF problem - provided the answers to all the above still indicate it should work. The query should work. I've done it here.

Let us know what you can tell us.
Normally the Update command is used as:
Update table_name
Set field_name = new_field
Where 'conditional_statements'

however it appears that you are attempting to set a field value equal to an entire row value which is what is returned from the query.

Or try this, as in your case:

UPDATE IIM                              
SET IPFDV=  
WHERE                            
(SELECT IPLC FROM IIMX WHERE IIMX.IXPROD=IIM.IPROD)  



never mind...i read my own notes wrong

Commented:
Have you ever used Microsoft Access and ODBC to connect to AS/400 files.

If you need help let me know.  It will make this task much easier.

My comment on Feb 16 regarding subqueries in Update came straight out the help files.
vjg

Commented:
MFalcon,

What help files did this come from? It works here and I find no such restriction in any manual I can get my hands on.

 - vjg

Commented:
>> vjg
From V3R7.  IBM only implemented the additional functionality in later releases.
vjg what release are you on?
keynes is on V4R2

Michael
vjg

Commented:
MFalcon

I'm at V4R3. Keynes is at V4R2 but states that he/she referenced V4R4 manuals. I'd say we're in a "not supported at that release" situation.

Anyone feel otherwise?

Author

Commented:
I want to get the exact evidence about this.

Author

Commented:
BTW, I have tried to link a table to AS400 using ACCESS and ODBC, but it is too too  slow.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:
vjg

Thank you for your humour in 'How's that for exact.'

You gave me a good laugh.

Keynes
How are you connected to your AS/400.  Is it via twin-ax or ethernet.  Or are you connected via dial-up.
My connection is via ethernet LAN and we use TCP/IP.  A transfer of 1MB takes 5 minutes on my config.
Only server models can transfer data at higher rates.

When I use Access, I do not use joins with ODBC connected tables as this does slow the link down severly.  The ODBC driver is not optimised.  You will also need to make sure that you use indexes.

Michael

vjg

Commented:
Um, Keynes, have you found the proposed answer to be a correct response?
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.