I have an update statement shown below that works fine, I used a with statement in the subquery to greatly improve performance but for some reason I'm not allowed to add an additional column from the same table to update.
Works:
UPDATE Table_A SET (Col_One) = (WITH OneValue AS (SELECT DISTINCT t.Col_One
FROM Table_Two t, Table_A a
WHERE t.id = a.New_Id))
SELECT Col_One FROM OneValue);
What I'd like to do is just include another column to update also from table_two like this
UPDATE Table_A SET (Col_One, Col_Two) = (WITH OneValue AS (SELECT DISTINCT t.Col_One, T.Col_two
FROM Table_Two t, Table_A a
WHERE t.id = a.New_Id))
SELECT Col_One, Col_Two FROM OneValue);
but I get ora-01767 update set expression must be a subquery. I understand this error but fail to see how I'm generating it. Any help is greatly appreciated.
Thanks in advance.
This appears to work (it did with a simple query using DUAL anyway):
UPDATE Table_A SET (Col_One, Col_Two) = (select col_one, col_two from
(WITH OneValue AS (SELECT DISTINCT t.Col_One, T.Col_two
FROM Table_Two t, Table_A a
WHERE t.id = a.New_Id))
SELECT Col_One, Col_Two FROM OneValue)
);
As for why it doesn't work if the subquery starts with "WITH", I can only imagine that it is because the designers of Oracle SQL hadn't anticipated this usage.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With