CODEWITHSUNDEEP
sqloracle-database
Nick
Nick

Reputation: 57

Updating database table information issue

So I've got a table called 'price' that has the columns 'dbName', dbRetailer' and 'dbPrice'.

I'm attempting to update 'dbPrice' by 10%, but only for the databases that were developed by 'Microsoft'

I also have 2 other table called 'dbSystem' and 'dbProducer'.

'dbName' is the foreign key for 'db_Name' which is the primary key in the table 'dbSystem'

Inside 'dbSystem' there is a foreign key called 'producerName' which points to the primary key 'producer_Name' in 'dbProducer' which is the producers name (eg Microsoft).

So my question is how do I go about creating an update query that will only update the databases that are developed by 'Microsoft'?

So far I have the code:

update price set dbPrice = dbPrice * 1.1 where producer_Name = 'Microsoft';

But this has no way of implementing the path to the information the query requires, which means I get the error like

"producer_Name: invalid identifier".

Any help with this would be greatly appreciated!

Upvotes: 0

Views: 49

Answers (2)

schurik
schurik

Reputation: 7928

 update price 
 set dbPrice = dbPrice * 1.1 
 where dbName in ( select dbName 
                   from dbSystem 
                   where producer_Name = 'Microsoft'
                 )
;

Upvotes: 0

Aleksej
Aleksej

Reputation: 22949

If I understand well, you may need a MERGE.

setup:

create table price (dbName varchar2(100), dbRetailer varchar2(100), dbPrice number);
create table dbSystem (dbName varchar2(100), producer_Name varchar2(100));
create table dbProducer ( producer_Name varchar2(100));
insert into dbProducer values ('Microsoft');
insert into dbProducer values ('Other');
insert into dbSystem   values ('Microsoft Product 1', 'Microsoft');
insert into dbSystem   values ('Microsoft Product 2', 'Microsoft');
insert into dbSystem   values ('Another Product',     'Other');
insert into price      values ('Microsoft Product 1', 'Retailer', 100);
insert into price      values ('Microsoft Product 2', 'Retailer', 200);
insert into price      values ('Another Product',     'Retailer', 50);

The sql:

merge into price P
using (
        select *
        from dbSystem
             inner join dbProducer using(producer_name)
        where producer_name = 'Microsoft'
      ) M
on (M.dbName = P.dbName)
when matched then
update
set dbPrice = dbPrice * 1.1

Here I used dbProducer to apply the filter on 'Microsoft', but if the value you need to filter for are already in the PK, you can make it simpler:

merge into price P
using (
        select *
        from dbSystem
        where producer_name = 'Microsoft'
      ) M
on (M.dbName = P.dbName)
when matched then
update
set dbPrice = dbPrice * 1.1

Upvotes: 1

Related Questions