CODEWITHSUNDEEP
sqloracle-databasesql-update
miracle
miracle

Reputation: 535

Update and nested select statement?

I want to update prices of those products thats not been purchased by 1 year. How do I do that?

My current query is:

UPDATE product 
   SET price = price * 0.9 
 WHERE date_purchase > SYSDATE - 365 
   AND pid IN ([How do i select the items thats not been purchased in 1year??]);

I have 2 tables:

Upvotes: 1

Views: 2897

Answers (2)

Gary Myers
Gary Myers

Reputation: 35401

I'd go with a NOT EXISTS as it makes the requirement more transparent.

update product 
set price = price * 0.9 
where not exists
  (select 1 from PURCHASE pchase
  WHERE pchase.pid = PRODUCT.pid
  and pchase.date_purchase > add_months(sysdate,-12))

of course you would want to consider what to do with products that have only been just introduced (eg a week old) and never sold.

Upvotes: 2

rene
rene

Reputation: 42494

I think this might come close

update product 
set price = price * 0.9 
where  pid NOT IN (
   select pr.pid 
   from product pr
   left outer join purchase pu   
   on pu.pid = pr.pid
   where (( pu.date_purchase != null) 
           AND pu.date_purchase < (SYSDATE - 365))
   or pu.pid == null
);

Upvotes: 0

Related Questions