Oracle SQL: Updating a data by 15% -

i trying update stock prices items there 15% stock increase. below code create tables (i have inserted 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 , 9999.99),  constraint category_reorderlevel_cc check (reorderlevel between -1 , 99) ); 

here's current code update prices 15% (separate workspace):

update stock set stock.unitcost = (stock.unitcost * 1.15) warehouse.warehouseid = 'mel'; 

it comes error:

error @ command line : 3 column : 7 error report - sql error:   ora-00904: "warehouse"."warehouseid": invalid identifier   00904. 00000 -  "%s: invalid identifier"   *cause:      *action:   

currently have no relationship between stock , warehouse. without defined relationship cannot update 1 table using criteria other.

if given item of stock held in 1 warehouse add warehouseid stock table. in case update this:

update stock s set s.unitcost = (s.unitcost * 1.15) s.warehouseid = 'mel' ; 

more stock items can held in more 1 warehouse, in case need third table. table store how stock 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 need remove column unitsonhand stock table doesn't belong there. allocating central amount of stock between different warehouses left exercise reader.

now update be:

update stock s set s.unitcost = (s.unitcost * 1.15) s.stockno in ( select ws.stockno warehouse ws                      .ws.warehouseid = 'mel') ;