CODEWITHSUNDEEP
oracle-database
Sachin
Sachin

Reputation: 21921

Getting Error in query

update tablename set (col1,col2,col3) = (select col1,col2,col3 from tableName2 order by tablenmae2.col4) return error Missing ). The query works fine if I remove the order by clause

Upvotes: 0

Views: 102

Answers (2)

Dave Costa
Dave Costa

Reputation: 48121

ORDER BY is not allowed in a subquery within an UPDATE. So you get the error "Missing )" because the parser expects the subquery to end at the point that you have ORDER BY.

Upvotes: 2

Jonathan Leffler
Jonathan Leffler

Reputation: 754870

What is the ORDER BY intended to do?

What you probably have in mind is something like:

UPDATE TableName
   SET (Col1, Col2, Col3) = (SELECT T2.Col1, T2.Col2, T2.Col3
                               FROM TableName2 AS T2
                              WHERE TableName.Col4 = T2.Col4
                            )
 WHERE EXISTS(SELECT * FROM TableName2 AS T2 WHERE TableName.Col4 = T2.Col4);

This clumsy looking operation:

  1. Grabs rows from TableName2 that match TableName on the value in Col4 and updates TableName with the values from the corresponding columns.
  2. Ensures that only rows in TableName with a corresponding row in TableName2 are altered; if you drop the WHERE clause from the UPDATE, you replace the values in Col1, Col2, and Col3 with nulls if there are rows in TableName without a matching entry in TableName2.

Some DBMS also support an update-join notation to reduce the ghastliness of this notation.

Upvotes: 0

Related Questions