更新和嵌套选择语句？

发布于 2024-10-20 16:34:42 字数 365 浏览 1 评论 0原文

我想更新那些一年内未购买的产品的价格。我该怎么做？

我当前的查询是：

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??]);

我有 2 个表：

Product => pid、p_name 等...（pid = 产品 ID，p_name = 产品名称）
购买 => pid、购买日期等

原文

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:

Product => pid, p_name, etc... (pid = product id, p_name = product name)
Purchase => pid, date_purchase, etc

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

以可爱出名 2024-10-27 16:34:42

我会选择 NOT EXISTS，因为它使要求更加透明。

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))

当然，您会想考虑如何处理刚刚推出（例如一周前）且从未售出的产品。

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.

回复收藏 0 原文

怀里藏娇 2024-10-27 16:34:42

我认为这可能会接近

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
);

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
);

回复收藏 0 原文

~没有更多了~