Update 1 field in a table from another field in a different table (OS400, not a 1 to 1 relationship)

Question

Im trying to update a field in a table from another field in a different table.

The table being updated will have multiple records that need updating from 1 match in the other table.

Example, i have a 1 million row sales history file. Those million records have aproximately 40,000 different sku codes, each row has a date and time stamp. Each sku will have multiple records in there.

I added a new field called MATCOST (material cost).

I have a second table containing SKU and the MATCOST.

So i want to stamp every line in table 1 with the corresponding SKU's MATCOST in table2. I cannot seem to achieve this when its not a 1 to 1 relationship.

This is what i have tried:

update 
  aulsprx3/cogtest2 
set 
  matcost = (select Matcost from queryfiles/coskitscog where 
  aulsprx3/cogtest2.item99 = queryfiles/coskitscog.ITEM ) 
where 
  aulsprx3/cogtest2.item99=queryfiles/coskitscog.ITEM

But that results in the SQL error: Column qualifier or table COSKITSCOG undefined and highlighting the q in the last reference to queryfiles/coskitscog.Item

Any idea's ?

Kindest Regards

Adam

Update: This is what my tables look like in principle. 1 Table contains the sales data, the other contains the MATCOSTS for the items that were sold. I need to update the Sales Data table (COGTEST2) with the data from the COSKITCOG table. I cannot use a coalesce statement because its not a 1 to 1 relationship, most select functions i use result in the error of multiple selects. The only matching field is Item=Item99

I cant find a way of matching multiple's. In the example we would have to use 3 SQL statements and just specify the item code. But in live i have about 40,000 item codes and over a million sales data records to update. If SQL wont do it, i suppose i'd have to try write it in an RPG program but thats way beyond me for the moment.

Thanks for any help you can provide.

Answer 1

Ok this is the final SQL statement that worked. (there were actually 3 values to update)

    UPDATE atst2f2/SAP20 ct                                  
       SET VAL520 = (SELECT cs.MATCOST                       
                    FROM queryfiles/coskitscog cs           
                    WHERE cs.ITEM = ct.pnum20),
           VAL620 = (SELECT cs.LABCOST                       
                    FROM queryfiles/coskitscog cs           
                    WHERE cs.ITEM = ct.pnum20),
           VAL720 = (SELECT cs.OVRCOST                       
                    FROM queryfiles/coskitscog cs           
                    WHERE cs.ITEM = ct.pnum20),             
       WHERE ct.pnum20 IN (SELECT cs.ITEM                    
                       FROM queryfiles/coskitscog cs)

Answer 2

This more compact way to do the same thing should be more efficient, eh?

UPDATE atst2f2/SAP20 ct                                  
   SET (VAL520, VAL620, VAL720) = 
       (SELECT cs.MATCOST, cs.LABCOST, cs.OVRCOST                      
                FROM queryfiles/coskitscog cs           
                WHERE cs.ITEM = ct.pnum20)             
   WHERE ct.pnum20 IN (SELECT cs.ITEM                    
                   FROM queryfiles/coskitscog cs)