| Update with outer join - Invalid column name [message #21628] |
Mon, 19 August 2002 09:27  |
Pascale Claeyman
Messages: 1
Registered: August 2002
|
Junior Member |
|
|
Hi!
I'm a SQL Server developer trying to translate a simple update statement to Oracle.
Well it seems I am doing something wrong but I dont know what...
The following statement returns
"Error: ORA-00904: invalid column name (State:S0022, Native Code: 388)"
UPDATE
(
SELECT *
FROM A, B
WHERE A.plot_fk IN (3)
AND A.status = 2
AND ( B.Col1 (+) = A.Col1
AND B.Col2 (+) = A.Col2
AND B.Col2 IS NULL)
)
SET A.status = 3
Basically, I want to set A.Status to 3 when I have no instance in B for A.
I can't use a "where not exists bla bla" because I'm playing with millions of rows, that's way too slow.
I run Oracle 8.1.6.
Any idea?
Thanx a lot!
Pascale.
|
|
|
|
| Re: Update with outer join - Invalid column name [message #21630 is a reply to message #21628] |
Mon, 19 August 2002 10:07  |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
First of all, you can not update a query_like (not physical) table for a change of column content. Oracle does not allow you to do so (as the sytax is: UPDATE table_name SET column_name = value [[WHERE conditions...]]).
In your case, to update a row of tabelA where its instance is not present in tableB, you can use it otherway round. But DO NOT use correlated query which is slow and causes the query to check each and every row of it for the number rows in the other table. Use MINUS operator to filter out the rows which are not present in both the tables and then use that output in the query of your UPDATE statement.
For example,
Assuming your TableA is bigger than the TableB (where some of the TableA rows are not present and the table size is comparatively lower than the TableA)
UPDATE TableA SET ColumnA = Value where Col1 =any
(SELECT col1 FROM TahleA
MINUS
SELECT col1 from TableB);
If TableB is larger than the TableA, then reverse the SELECTs in the above subquery.
Since it is not correlated query and the query is evaluated first, the update operation would be far better than the one that uses simple NOT EXISTS condition. To ensure that the inner query executes first, I guess, you can use a hint /*+ ORDER */ in your UPDATE statement. I guess it is ORDER hint, but any way, check it out in the ORACLE reference guide to make sure ORDER hint is right one for what I am talking about.
Good luck :)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
