Batch update a column from a Select in AS/400

Question

I am trying to use this update statement in order to fill a column Name in a table called MD001 from a table called Patient

MD001(MRN, Name, Address)

Patient(CaseNo, FName, MName, LName,...)

UPDATE QS36F.MD001 m 
SET NAME = 
        (
        SELECT FName || ' ' || Mname || ' ' || LName
        FROM QS36F.PATIENT
        WHERE fname <> '' AND mname <> '' AND lname <> ''
        )
        where  m.MRN  = (SELECT caseno FROM qs36f.patient WHERE caseno = m.MRN) 

I keep getting Result of SELECT more than one row. yet in both tables MRN and CaseNo are primary keys.

Answer 1

Your sub-select returns all the rows that have a non-blank name.

Try this:

UPDATE QS36F.MD001 m 
SET NAME = (
    SELECT FName || ' ' || Mname || ' ' || LName
    FROM QS36F.PATIENT
    WHERE caseno = m.MRN
)
WHERE  m.MRN IN (
    SELECT caseno 
    FROM qs36f.patient 
    WHERE fname <> '' AND mname <> '' AND lname <> ''
) 

I basically switched the where clauses. The outer where clause selects all the patients that you want to update, and the inner where clause joins specific records for update.