Geek asks and answers

Why can't I update more than one column at a time with the keyword "C"?

I have the update instruction shown below that works fine, I used the with statement in the subquery to significantly improve performance, but for some reason I am not allowed to add an extra column from the same table for the 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 would like to do is simply add 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 the expression ora-01767, which should be a subquery. I understand this error, but I don’t see how I generate it. Any help is appreciated.

Thanks in advance.

+5
source share
1 answer

, ( DUAL):

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

, , "WITH", , , Oracle SQL .

+8

More articles:


All Articles