Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

update table

SOLVED
Go to solution
Ratzie
Super Advisor

update table

I can not seem to get this to work.
update table1
set table1.cola ='PEPSI'
where table2.feature = 'PEPSI TYPE'
and table1.master_key = table2.master_key

So I am updating the main table if the second table contains the right data.
16 REPLIES
Aussan
Respected Contributor

Re: update table

Try " instead of '

example "PEPSI"
The tongue weighs practically nothing, but so few people can hold it
Aussan
Respected Contributor

Re: update table

here is the example

UPDATE table1
SET table1.cola="PEPSI"
WHERE tabel2.feature = "PEPSI TYPE" AND table1.master_key=table1.master_key
The tongue weighs practically nothing, but so few people can hold it
Aussan
Respected Contributor

Re: update table

here is the example

UPDATE table1
SET table1.cola="PEPSI"
WHERE tabel2.feature="PEPSI TYPE" AND table1.master_key=table1.master_key
The tongue weighs practically nothing, but so few people can hold it
Aussan
Respected Contributor

Re: update table

what happend?
The tongue weighs practically nothing, but so few people can hold it
TwoProc
Honored Contributor

Re: update table

What error do you get?

We are the people our parents warned us about --Jimmy Buffett
Ratzie
Super Advisor

Re: update table

same error
I have never used double quotes around values
Ratzie
Super Advisor

Re: update table

invalid sql statement
Aussan
Respected Contributor

Re: update table

what's the error??
The tongue weighs practically nothing, but so few people can hold it
Aussan
Respected Contributor

Re: update table

could you cut and past the exact syntax you are using, and also the error does it point at the exact line the error is or no
The tongue weighs practically nothing, but so few people can hold it
Ratzie
Super Advisor

Re: update table

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

Re: update table

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')
We are the people our parents warned us about --Jimmy Buffett
TwoProc
Honored Contributor

Re: update table

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

(oops)
We are the people our parents warned us about --Jimmy Buffett
Hein van den Heuvel
Honored Contributor

Re: update table

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
Honored Contributor

Re: update table

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.
We are the people our parents warned us about --Jimmy Buffett
Yogeeraj_1
Honored Contributor

Re: update table

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Ratzie
Super Advisor

Re: update table

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