Oracle SQL: Updating a data by 15%

Question

I am trying to update stock prices for items for which there is a 15% stock increase. Below is my code to create the tables (I have already inserted all my data):

CREATE TABLE Warehouse
(WarehouseID CHAR(3),
 LocationCity VARCHAR2(25) NOT NULL,
 LocationState CHAR(3) NOT NULL,
 CONSTRAINT Warehouse_WarehouseID_PK PRIMARY KEY (WarehouseID),
 CONSTRAINT Warehouse_WarehouseID_CC CHECK (UPPER(WarehouseID) IN ('MEL', 'SYD', 'BNE', 'HBA', 'ADL', 'PER', 'DIA')),
 CONSTRAINT Warehouse_LocationState_CC CHECK (UPPER(LocationState) IN ('QLD', 'NSW', 'VIC',  'TAS',  'ACT',  'SA',  'WA',  'NT'))
);

CREATE TABLE Stock
(StockNo CHAR(4),
 StockDesc VARCHAR2(50) NOT NULL,
 UnitsOnHand NUMERIC DEFAULT 0 NOT NULL,
 ItemCategory CHAR(2),
 UnitCost NUMERIC(4,2) DEFAULT 0.00 NOT NULL,
 ReorderLevel NUMERIC(2) DEFAULT 0 NOT NULL,
 CONSTRAINT Category_StockNo_PK PRIMARY KEY (StockNo),
 CONSTRAINT Category_ItemCategory_FK FOREIGN KEY (ItemCategory) REFERENCES "CATEGORY"(CategoryID),
 CONSTRAINT Category_StockDesc_UK UNIQUE (StockDesc),
 CONSTRAINT Category_UnitCost_CC CHECK (UnitCost BETWEEN 0.00 AND 9999.99),
 CONSTRAINT Category_ReorderLevel_CC CHECK (ReorderLevel BETWEEN -1 AND 99)
);

Here's the current code to update prices by 15% (separate workspace):

UPDATE Stock
SET Stock.UnitCost = (Stock.UnitCost * 1.15)
WHERE Warehouse.WarehouseID = 'MEL';

It comes up with this error:

Error at Command Line : 3 Column : 7 Error report - SQL Error:  
ORA-00904: "WAREHOUSE"."WAREHOUSEID": invalid identifier  
00904. 00000 -  "%s: invalid identifier"  
*Cause:     
*Action:

You missed to Join `WAREHOUSE` table. What is the common column between `Stock` and `WAREHOUSE` table or How will you relate `WAREHOUSE` table with `Stock` table — Pரதீப், May 31 '15 at 05:56
check here http://stackoverflow.com/questions/2446764/oracle-update-statement-with-inner-join — Pரதீப், May 31 '15 at 06:27

APC · Answer 1 · 2015-06-01T06:41:53.093

Currently you have no relationship between Stock and Warehouse. Without a defined relationship you cannot update the one table using criteria from the other.

If any given item of stock is held in only one warehouse you could just add WarehouseID to the STOCK table. In that case your update would be this:

UPDATE stock s
SET s.UnitCost = (s.UnitCost * 1.15)
WHERE s.WarehouseID = 'MEL'
;

More likely Stock items can be held in more than one Warehouse, in which case you need a third table. This table will store how much stock is held in each warehouse.

create table warehouse_stock ( 
   WarehouseID CHAR(3) not null,
   StockNo CHAR(4) not null,
   UnitsOnHand NUMERIC DEFAULT 0 NOT NULL,
    constraint warehouse_stock_pk primary key (WarehouseID, StockNo),
    constraint warehouse_stock_warehouse_fk foreign key (WarehouseID)
        references warehouse(warehouseID),
    constraint warehouse_stock_stock_fk foreign key (StockNo)
        references stock(stockNo)
)

Note that you need to remove the column UnitsOnHand from STOCK table as it doesn't belong there. Allocating this central amount of stock between different warehouses is left as an exercise for the reader.

Now your update will be:

UPDATE stock s
SET s.UnitCost = (s.UnitCost * 1.15)
WHERE s.stockno in ( select ws.stockno from Warehouse ws
                     where .ws.WarehouseID = 'MEL')
;

Oracle SQL: Updating a data by 15%

1 Answers1