Nick Nick - 2 months ago 18
SQL Question

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!

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