[ 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(SELECT * FROM Orders WHERE Amount > 250 HAVING COUNT(*) >= 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
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:
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;