Update does not working while select returns right results

Posted on

Question :

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?

Answer :

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

Leave a Reply

Your email address will not be published. Required fields are marked *