Solved

Using subquery in update of SQL?

Posted on 2000-02-14
25
663 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how in apache config file makes it run php on iseries? 2 433
ODBC Issues with Microsoft Access 2010 6 367
AS400 Forms 9 112
AS400 Logging Print device 2 73
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Large Outlook files lead to various unwanted errors and corruption issues. Furthermore, large outlook files can also make Outlook take longer to start-up, search, navigate, and shut-down. So, In this article, i will discuss a method to make your Out…
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…
A simple description of email encryption using a secure portal service. This is one of the choices offered by The Email Laundry for email encryption. The other choices are pdf encryption which creates an encrypted pdf of your email and any attachmen…

919 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

16 Experts available now in Live!

Get 1:1 Help Now