Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

Tables Example

like image 385
ADAM MARSHALL Avatar asked Oct 12 '12 15:10

ADAM MARSHALL


2 Answers

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)
like image 149
ADAM MARSHALL Avatar answered Sep 23 '22 12:09

ADAM MARSHALL


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)
like image 29
WarrenT Avatar answered Sep 22 '22 12:09

WarrenT