update table

[Ratzie]

ORA-00900
ORA-00904 invalid column name.
But, the columns are correct!
It is complaining on the master_key columns

[TwoProc · Accepted solution]

What error do you get?

This should do it for you

update tab1e1
set table1.cola = 'PEPSI'
where exists
( select 'x' from
table2 where table1.masterkey =
table2.masterkey
and table2.feature='PEPSI TYPE')

[TwoProc]

that's:
"update table1"
not
"update tab1e1" at the statement beginning of course...

(oops)

[Hein van den Heuvel]

Hi Laura,

For this question and many others like it, may I recommend "The SQL Cookbook" by Anthony Molinaro or any similar book:

http://www.oreilly.com/catalog/sqlckbk/

The question you ask is described there on page 71 "Updating when Corresponding Rows Exist"

Alternative to the 'exists' clause it to use an 'in' clause.
Untested...

update table1
set cola = 'PEPSI'
where masterkey in
( select masterkey
from table2
where feature = 'PEPSI TYPE');

Hope this helps some,
Hein van den Heuvel (at gmail dot com)
HvdH Performance Consulting

[TwoProc]

FWIW,

For the sake of correctness - Hein's posting is much more common, and is a better answer to your problem.

The reason that I gave my answer is that this is something I may try to use to when attempting to tune a piece of someone else's code and get more performance out of a query that is being troublesome due to some issue I would be seeking to change in the execution plan. Keeping in mind that method is used more for isolating subqueries in a purposeful manner (and not development in general), you should use primarily use Hein's approach, it's more standard. My suggested method would normally add unnecessary verbage and an extra step to an otherwise normally fine execution plan.

[Yogeeraj_1]

Hi,

I agree with John that Hein's solution is more appropriate.

yd@MYDB.MU> create table table1 (master_key number(1), cola varchar2(10));

Table created.

Elapsed: 00:00:02.29
yd@MYDB.MU> create table table2 (master_key number(1), feature varchar2(10));


Table created.

Elapsed: 00:00:00.45
yd@MYDB.MU> insert into table2 values (1,'PEPSI TYPE');

1 row created.

Elapsed: 00:00:00.03
yd@MYDB.MU> insert into table1 values (1,'');

1 row created.

Elapsed: 00:00:00.02
yd@MYDB.MU> insert into table1 values (2,'');

1 row created.

Elapsed: 00:00:00.06
yd@MYDB.MU> insert into table1 values (1,'');

1 row created.

Elapsed: 00:00:00.01
yd@MYDB.MU> update table1
2 set cola='PEPSI'
3 where master_key in (select master_key from table2
4 where feature='PEPSI TYPE');

2 rows updated.

Elapsed: 00:00:00.09
yd@MYDB.MU> select * from table1;

1 PEPSI
2
1 PEPSI

Elapsed: 00:00:00.04
yd@MYDB.MU>

kind regards
yogeeraj

[Ratzie]

I agree with Hein also.
I tried the where exists clause and completely hung my session.
Where the " Where in" clause took no time to return!

Saved it as a gem!
Thanks all