TAGS :Viewed: 7 - Published at: a few seconds ago

[ beginner sql question pt.2 error ora-00933 SQL command not properly ended ]

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 Orders
           WHERE Amount > 250
           HAVING COUNT(*) >= 2);

Any ideas?

Answer 1

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

Answer 2

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;

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:

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