Help with UPDATING a column

From: Joe <RappaJ_at_nycha.nyc.gov>
Date: 28 Nov 2003 07:18:50 -0800
Message-ID: <ab8e8633.0311280718.1d3d2429@posting.google.com>

Hi,

Using the 2 tables below, I need to write an update query to populate the
UPDU column in material_usage_history b table...

The calculation is:
 (SUM(B.TRANS_QTY)/(A.DWELLING_UNITS)*100) So I probably will have to group by borough, item_number, to_loc, loc_name, dwelling_units

---

This SELECT statement works just fine and gives me what I need:

select b.item_number, a.borough, a.to_loc, a.loc_name, a.dwelling_units,
sum(b.TRANS_QTY) AS TRANS_QTY ,
SUM(B.TRANS_QTY)/(A.DWELLING_UNITS)*100) UPDU   from location_master a, material_usage_history b     where a.to_loc = b.to_loc
    group by a.borough, b.item_number, a.to_loc, a.loc_name, a.dwelling_units ;

ITEM_NUMBE BOROUGH       TO_L LOC_NAME                        
DWELLING_UNITS  TRANS_QTY       UPDU
---------- ------------- ---- --------------------------------
-------------- ---------- ----------
101973572 BRONX          0218 THROGGS NECK                            
  1185          7   .5907173
0101973572 BRONX         0229 HIGHBRIDGE GARDENS                      
   700          4 .571428571
0101973572 BRONX         0234 JAMES MONROE                            
  1102          1 .090744102


**************************************


desc material_usage_history b
 Name         Type

• ------------- ITEM_NUMBER VARCHAR2(10) YEAR NUMBER(4) MONTH NUMBER(2) TO_LOC VARCHAR2(4) FROM_LOC VARCHAR2(4) TRANS_QTY NUMBER(6) UPDU NUMBER(11,5)

desc location_master a
 Name Type

• ------------- TO_LOC VARCHAR2(4) LOC_TYPE VARCHAR2(14) LOC_NAME VARCHAR2(32) OLD_DEPT# VARCHAR2(5) NEW_DEPT# VARCHAR2(7) BOROUGH VARCHAR2(13) DWELLING_UNITS NUMBER(5) RESP_EDP NUMBER(4) HUD# VARCHAR2(14)

  ---

This is all that I have...

update material_usage_history b

   set b.UPDU =

---

Thanks,
Joe Received on Fri Nov 28 2003 - 09:18:50 CST