Thanks for your answer about using the WITH clause in an UPDATE statement, but it seems the implementation is pretty limited, no?
I want to do something like the dummy code below, where I can re-use the WITH cause in
both the SET and WHERE clauses. I was hoping for the performance benefit you sometimes get when you use the WITH clause multiple times.
But Oracle 12c won't seem to let me do that (at least with the syntax I tried). I tried to identify the WITH up front, so I can use it both places, but Oracle complains:
ORA-00928: missing SELECT keyword
In this fictitious example, let's say my hardware store's database keeps the order date on the order detail lines, as well as in the order header. (Yes, bad data model, but it's not real so extra CPU cycles were consumed.)
Now if the header record's order date gets changed, they want me to update the order date in the details to match. If I don't use the WITH clause, I'm looking at the order header record twice for each order item/detail record.
WITH new_header AS (SELECT oh.order_number,
oh.order_date
FROM order_header)
UPDATE order_items
SET order_items.order_date = (SELECT new_header.order_date
FROM new_header
WHERE new_header.order_number = order_items.order_number)
WHERE order_items.order_date <> (SELECT new_header.order_date
FROM new_header
WHERE new_header.order_number = order_items.order_number)Do you have any suggestions for a different syntax?
Thanks in Advance