flauto
asked on
UPDATE in DB2400
How could I execute this SQL Server command in DB2400?
UPDATE AS400.BILLI1.MAXDAD.LI060A
FROM AS400.BILLI1.MAXDAD.LI060A
WHERE A060CODCLI = U005CODCLI AND
A060CODMER = U005CODMER
UPDATE AS400.BILLI1.MAXDAD.LI060A
FROM AS400.BILLI1.MAXDAD.LI060A
WHERE A060CODCLI = U005CODCLI AND
A060CODMER = U005CODMER
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.
ASKER
I WANT JUST SAY THE CORRECT SINTAX FOR THAT COMMAND IN DB2400.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.U00
A060CODDST = (SELECT U005CODDST FROM FILE1 INNER JOIN FILE2 ON FILE1.A060CODCLI=FILE2.U00
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.
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.U00
A060CODDST = (SELECT U005CODDST FROM FILE1 INNER JOIN FILE2 ON FILE1.A060CODCLI=FILE2.U00
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.
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.