Solved

Using subquery in update of SQL?

Posted on 2000-02-14
25
660 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
  • 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 50 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

HOW TO: Connect to the VMware vSphere Hypervisor 6.5 (ESXi 6.5) using the vSphere (HTML5 Web) Host Client 6.5, and perform a simple configuration task of adding a new VMFS 6 datastore.
A Short Story about the Best File Recovery Software – Acronis True Image 2017
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now