We help IT Professionals succeed at work.

UPDATE in DB2400

flauto
flauto asked
on
Medium Priority
669 Views
Last Modified: 2007-12-19
How could I execute this SQL Server command in DB2400?

UPDATE AS400.BILLI1.MAXDAD.LI060A1 SET A060CODVEN = U005CODVEN, A060CODDST = U005CODDST
FROM AS400.BILLI1.MAXDAD.LI060A1, AS400.BILLI1.MAXDAD.LI005U1
WHERE A060CODCLI = U005CODCLI AND
      A060CODMER = U005CODMER
Comment
Watch Question

Bob ButcheriSeries Engineer

Commented:
Two ways I can think of to do this. Either:
 
     1) use SQL/400 if you have the licensed program on the AS400.

     2) use Client Access's Operations Navigator. Select AS400 you have the database on. Expand the AS400 tree. Find Database. Right click on the Database tree. Click on run SQL Scripts. You should be able to run it there.



Bob ButcheriSeries Engineer

Commented:
Another way, if you have Client Access installed for OLE-DB and ODBC, create VB program and execute using ADO. This is the way I execute my statements.

Author

Commented:
I WANT JUST SAY THE CORRECT SINTAX FOR THAT COMMAND IN DB2400.
Commented:
You could try something like this...

UPDATE AS400.BILLI1.MAXDAD.LI060A1
SET A060CODVEN = (SELECT U005CODVEN FROM   AS400.BILLI1.MAXDAD.LI005U1 WHERE A060CODCLI = U005CODCLI AND A060CODMER = U005CODMER ),
    A060CODDST = (SELECT U005CODDST FROM AS400.BILLI1.MAXDAD.LI005U1 WHERE A060CODCLI = U005CODCLI AND A060CODMER = U005CODMER)

Commented:
Hi,

 I'm going to suppose that you have SQL installed on your AS/400. Firstly, it looks like that you have a file with multiple member so you should do something like this :

OVRDBF FILE(FILE1) TOFILE(BILLI1/MAXDAD) MBR(LI060A1)
OVRDBF FILE(FILE2) TOFILE(BILLI1/MAXDAD) MBR(LI005U1)

Secondly, you have to type STRSQL to be in the "SQL shell". I'm going to assume that AS400 in your instruction is a way to designate an AS/400 on the network. So, in that case you should write something like this :

UPDATE FILE1
SET A060CODVEN =
(SELECT U005CODVEN FROM   FILE1 INNER JOIN FILE2 ON FILE1.A060CODCLI=FILE2.U005CODCLI  AND FILE1.A060CODMER=FILE2.U005CODMER)

A060CODDST = (SELECT U005CODDST FROM   FILE1 INNER JOIN FILE2 ON FILE1.A060CODCLI=FILE2.U005CODCLI  AND FILE1.A060CODMER=FILE2.U005CODMER)


Of course you can type these instruction in a "text" file and you can execute the RUNSQLSTM. In the STRSQL "shell" you can have some help if you press the F4 key. It will give you all the AS/400 SQL instruction that you can run in a interactive session.

Hope this help.

Bye.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.