?
Solved

Using subquery in update of SQL?

Posted on 2000-02-14
25
Medium Priority
?
674 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.

0
Comment
Question by:keynes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 4
  • +3
25 Comments
 
LVL 3

Expert Comment

by:P_S_Price
ID: 2524978
Does the select return more than one row if you run it manually?
0
 
LVL 1

Expert Comment

by:MFalcon
ID: 2525281
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.
0
 

Author Comment

by:keynes
ID: 2525471
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?
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 3

Expert Comment

by:P_S_Price
ID: 2528588
Does Syntax check fail or does the Execution Fail? Does the select return more than one row??
0
 

Author Comment

by:keynes
ID: 2529516
SYNTAX CHECK FAIL
0
 

Expert Comment

by:vjg
ID: 2537173
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.
0
 
LVL 3

Expert Comment

by:P_S_Price
ID: 2540910
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
0
 

Author Comment

by:keynes
ID: 2541187
No. iplc is in IIMX
0
 
LVL 3

Expert Comment

by:P_S_Price
ID: 2547468
Please Post Exact Text of Message !!
0
 
LVL 1

Expert Comment

by:jsuper
ID: 2555714
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.
0
 

Expert Comment

by:vjg
ID: 2555760
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.
0
 

Expert Comment

by:vjg
ID: 2555794
Do you have the latest Database Fix Pack installed. These are NOT distributed with Cume tapes.
0
 

Author Comment

by:keynes
ID: 2571592
error message:

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

Expert Comment

by:vjg
ID: 2571675
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.
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 2582628
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)  



0
 
LVL 28

Expert Comment

by:AzraSound
ID: 2582630
never mind...i read my own notes wrong
0
 
LVL 1

Expert Comment

by:MFalcon
ID: 2589377
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.
0
 

Expert Comment

by:vjg
ID: 2589544
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
0
 
LVL 1

Expert Comment

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

Michael
0
 

Expert Comment

by:vjg
ID: 2589652
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?
0
 

Author Comment

by:keynes
ID: 2595176
I want to get the exact evidence about this.
0
 

Author Comment

by:keynes
ID: 2595179
BTW, I have tried to link a table to AS400 using ACCESS and ODBC, but it is too too  slow.
0
 

Accepted Solution

by:
vjg earned 100 total points
ID: 2595876
Go to http://as400bks.rochester.ibm.com. Bring up the V4R3 manuals. Find the SQL manuals. I can't remember if I looked in the Reference or the User guide. In preface 3, "How this book has changed," it states that the three major new features added at V4R3 are

1. Precompiler support for C++
2. Subselect in Set Clause of Update Statement
3. Alias support.

Number 2 is your answer. This feature was added at V4R3.

How's that for exact.
0
 
LVL 1

Expert Comment

by:MFalcon
ID: 2598425
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

0
 

Expert Comment

by:vjg
ID: 2613175
Um, Keynes, have you found the proposed answer to be a correct response?
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a fine trick which I've found useful many times, when you just don't want to accidentally run a batch script or the commands needs administrator rights.
Check out the latest tech news, community articles, and expert highlights in August's newsletter.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month14 days, 17 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question