Updating database table information issue

Question

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!

Are your column names in both upper and lower case? http://stackoverflow.com/questions/7425153/reason-why-oracle-is-case-sensitive — Rene, Sep 21 '16 at 07:43
Yeah they are, but I don't think that's the problem as the rest of my code works fine all except this update statement. — Nick, Sep 21 '16 at 07:47

score 1 · Answer 1 · answered Sep 21 '16 at 07:52

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

So basically I have to create a whole new table so that the information can be access from one location? — Nick, Sep 21 '16 at 08:03
I simply added the script to create a new table to test it on my system. The query will work with your tables. If you have lower case names, simply edit my query adding double quotes and writing the names of tables and columns with the correct case — Aleksej, Sep 21 '16 at 08:20

score 0 · Accepted Answer · answered Sep 21 '16 at 08:57

0

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

answered Sep 21 '16 at 08:57

schurik

7,798
2
23
29

Are both of the "dbName" is your example correct? Is one of them suppose to be a "db_Name" at all? I tried your code, It didn't throw any errors but it didn't update the data either. – Nick Sep 21 '16 at 10:19
It was Correct! But i was right in saying that one of the "dbName" might be wrong. The "dbName" inside the bracket is suppose to be "db_Name" instead because that's the primary key reference to the table "dbSystem". But that mistake wasn't your fault, all the names are so similar that I confuse myself. Thanks again! – Nick Sep 21 '16 at 10:39

Updating database table information issue

2 Answers2