Using subquery in update of SQL?

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

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.

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?

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

SYNTAX CHECK FAIL

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.

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

No. iplc is in IIMX

Please Post Exact Text of Message !!

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.

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.

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

error message:

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

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

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.

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

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

Michael

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?

I want to get the exact evidence about this.

BTW, I have tried to link a table to AS400 using ACCESS and ODBC, but it is too too  slow.

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

Um, Keynes, have you found the proposed answer to be a correct response?