初学者sql问题pt.2错误ora-00933 SQL命令未正确结束

发布于 2024-10-18 05:14:36 字数 252 浏览 5 评论 0原文

我必须为 DBMS 中的作业更新表。不明白为什么我会收到此错误。

  UPDATE Customers
  SET CreditLimit = CreditLimit * 1.25
      FROM(SELECT *
           FROM Orders
           WHERE Amount > 250
           HAVING COUNT(*) >= 2);

有什么想法吗？

原文

I have to update my tables for my assignment in DBMS. Can't figure out why I get this error.

  UPDATE Customers
  SET CreditLimit = CreditLimit * 1.25
      FROM(SELECT *
           FROM Orders
           WHERE Amount > 250
           HAVING COUNT(*) >= 2);

Any ideas?

分享到QQ

分享到微博

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

发布评论

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

平生欢 2024-10-25 05:14:36

正如您所指定的，update 语句没有 from 子句。
你是否正在尝试做这样的事情：
对于至少有 2 个订单金额超过 250 美元的客户，将信用额度提高 25%。

update Customers
   set CreditLimit = CreditLimit * 1.25
 where (select count(*)
          from Orders
         where Amount > 250
           and orders.customer_id = Customers.customer_id)) >= 2;

编辑
我刚刚注意到您正在使用 Oracle（ORA 消息）。由于您可能要更新所有客户，我相信最有效的方法是使用“可更新连接”或如下合并语句：

merge 
 into customers
using (select customer_id
         from Orders o
        where amount > 250
        group 
           by customer_id
       having count(*) >= 2
      ) orders
    on(customers.customer_id = orders.customer_id)
when matched then
   update
      set customers.creditlimit = customers.creditlimit * 1.25;

The update statement doesn't have a from clause, like you specified.
Are you trying to do something like this:
Increase credit limit by 25% for customers who have at least 2 orders for more than 250 money.

update Customers
   set CreditLimit = CreditLimit * 1.25
 where (select count(*)
          from Orders
         where Amount > 250
           and orders.customer_id = Customers.customer_id)) >= 2;

Edit
I just noticed you are using Oracle (the ORA message). Since you are potentially updating all customers, I believe the most performant way would be to use an "updatable join", or a merge statement like below:

merge 
 into customers
using (select customer_id
         from Orders o
        where amount > 250
        group 
           by customer_id
       having count(*) >= 2
      ) orders
    on(customers.customer_id = orders.customer_id)
when matched then
   update
      set customers.creditlimit = customers.creditlimit * 1.25;

回复收藏 0 原文

小嗷兮 2024-10-25 05:14:36

UPDATE Customers
      SET CreditLimit = CreditLimit * 1.25
          FROM Customers
    Where Id in (
     select CustomerId 
     from orders 
     where  Amount > 250
     Group By CustomerId
     HAVING COUNT(*) >= 2);

或者

UPDATE Customers
      SET CreditLimit = CreditLimit * 1.25
          FROM Customers c
    Where (select count(*) from orders o where o.CustomerId = c.Id And Amount > 250) > =2

UPDATE Customers
      SET CreditLimit = CreditLimit * 1.25
          FROM Customers
    Where Id in (
     select CustomerId 
     from orders 
     where  Amount > 250
     Group By CustomerId
     HAVING COUNT(*) >= 2);

UPDATE Customers
      SET CreditLimit = CreditLimit * 1.25
          FROM Customers c
    Where (select count(*) from orders o where o.CustomerId = c.Id And Amount > 250) > =2

回复收藏 0 原文

~没有更多了~