1

I am trying to execute the query below:

UPDATE(
  SELECT Ord.Completed
  FROM Product P
  INNER JOIN Order1 Ord
  ON Ord.OrderID = P.OrderID
  WHERE EXISTS(
          SELECT *
          FROM Delivery D
          WHERE Delivery.Delivered = 0 AND P.ProductID = Delivery.DeliveryID
        )
)
SET Completed= 'NO';

If I run the SELECT part only, without UPDATE it returns the right results. When I try to execute the UPDATE query I get

"ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:
Error at Line: 2 Column: 250"

Any ideas?

  • I think you need SET Completed= 'YES' ; (remove the table name from that part) – ypercubeᵀᴹ Jan 24 '15 at 15:26
  • @ypercube I tried it but I still get the same error. (Question Updated) – IrishDog Jan 24 '15 at 15:31
  • What should happen if an order has several products, some of them delivered and some not? Should the order br set as 'Completed'? – ypercubeᵀᴹ Jan 24 '15 at 16:02
  • About the error, perhapos you are missing the statement separator (;). Not sure. But please answer the above question, I'm not sure your approach is correct. – ypercubeᵀᴹ Jan 24 '15 at 16:06
  • I get the same error with the semicolon(;) too. An order has several products, some delivered and some other not. I add the Completed attribute to the Order table the 'YES' as the default value. An order is Completed when all its products are delivered. If I remove the UPDATE clause, the SELECT returns the correct results, that is, the Orders that contains products not delivered. I cannot understand why that's not work for the UPDATE – IrishDog Jan 24 '15 at 16:27
0

Please try this:

UPDATE(
  SELECT Ord.OrderID, Ord.Completed
  FROM Order1 Ord
  WHERE EXISTS(
          SELECT *
          FROM Product P
            INNER JOIN Delivery D
              ON Delivery.Delivered = 0 AND P.ProductID = Delivery.DeliveryID
          WHERE Ord.OrderID = P.OrderID
        )
)
SET Completed = 'NO' ;

or without a derived table:

UPDATE Order1 Ord
SET Completed = 'NO' 
WHERE EXISTS(
        SELECT *
        FROM Product P
          INNER JOIN Delivery D
            ON Delivery.Delivered = 0 AND P.ProductID = Delivery.DeliveryID
        WHERE Ord.OrderID = P.OrderID
      );
  • Thank you very much @ypercube, your second solution worked fine for me. The first did not work, it returned the same error message but also the SELECT clause did not return correct data. I am running Oracle G11. Is there any possibility that it would run well in another rdbms such as mysql? – IrishDog Jan 24 '15 at 17:43
  • The 2nd query would run in mysql, the 1st no. I'll add more details in the answer later. – ypercubeᵀᴹ Jan 24 '15 at 17:49
  • About right and wrong results, I can't say anything without same sample data. It would be great if you could set up a sqlfiddle – ypercubeᵀᴹ Jan 24 '15 at 17:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.