Update a Table From Another Table?

[Guest.Visitor]

brewbob wrote: > Is it possible to update a column in one table using a "where" clause > that references another table? Bob, Yes it's definitely possible. I forget when the capability was first introduced, somewhere in V4, probably R2. The following is from the V5R2 manual: "It is also possible to update an entire row in one table with values from a row in another table. Suppose there is a master class schedule table that needs to be updated with changes that have been made in a copy of the table. The changes are made to the work copy and merged into the master table every night. The two tables have exactly the same columns and one column, CLASS_CODE, is a unique key column. UPDATE CL_SCHED SET ROW = (SELECT * FROM MYCOPY WHERE CL_SCHED.CLASS_CODE = MYCOPY.CLASS_CODE) This update will update all of the rows in CL_SCHED with the values from MYCOPY. " If you can't get it going after this, list your code and we'll take a look. One of the biggest problems is making sure you handle the criteria in the where section for the table to be updated; if you don't, it will try to put a null value in the field to be updated - which you probably don't want. Bill

[Guest.Visitor]

Bill, My problem is that the two tables only have one field (a unique key) in common, and I only need to update one field, not the entire row. This arose after someone accidentally deleted a batch of invoices that had not yet printed. I'm trying to use SQL to reset the print code field in table 1 (POHEADER) based on a timestamp value in Table 2 (POPRTTIME). I've fixed this already using OPENQRYF and RPG, but did not want to give up trying the SQL route. Here's one version of SQL (that doesn't work): UPDATE POHEADER SET POPRTCD = '1' (SELECT FROM POPRTTIME WHERE POORDER# = PPORDER# AND PPTIMESTMP = '2003-07-28-13.+ 22.15.02300') Thanks, Bob

[Guest.Visitor]

If you adjust your statement a little it should work. Try the example here; UPDATE POHEADER SET POPRTCD = '1' WHERE POORDER#= (SELECT PPORDER# FROM POPRTTIME WHERE PPORDER# = POORDER# AND PPTIMESTMP = '2003-07-28-13.+ 22.15.02300')

[Guest.Visitor]

Thanks, Mike. That seemed to do the trick.

[Guest.Visitor]

It is also possible to update field values in a table using multiple values from a table using a variation of the following. One must be careful that the subselect returns only one value.

---

Code

[Guest.Visitor]

Is it possible to update a column in one table using a "where" clause that references another table? I've tried various statements and ran them thru the ISQL Validate mode, but keep getting error messages. Also couldn't find any examples nor references to this in "SQL at Work" and the "SQL Developer's Guide". What I'm attempting to do is reset a print code field in one table, based on a timestamp field in another table. Is this possible? TIA, Bob

[Guest.Visitor]

update jorgen/miloma set lmmfit = (select mlbrem from jorgen/mitloc where mitloc.mlcono = miloma.lmcono and mitloc.mlitno = miloma.lmitno and mitloc.mlbano = miloma.lmbano) I'm trying to run this on an AS/400 but gets the following msg: Null values not allowed in column or variable LMMFIT. How shall I change my SQL-statement to get rid of this msg?