topic update table in Operating System - HP-UX

update table

Ratzie — Thu, 19 Apr 2007 11:41:18 GMT

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.

Re: update table

Aussan — Thu, 19 Apr 2007 12:14:27 GMT

Try " instead of '

example "PEPSI"

Re: update table

Aussan — Thu, 19 Apr 2007 12:17:48 GMT

here is the example

UPDATE table1
SET table1.cola="PEPSI"
WHERE tabel2.feature = "PEPSI TYPE" AND table1.master_key=table1.master_key

Re: update table

Aussan — Thu, 19 Apr 2007 12:18:20 GMT

here is the example

UPDATE table1
SET table1.cola="PEPSI"
WHERE tabel2.feature="PEPSI TYPE" AND table1.master_key=table1.master_key

Re: update table

Aussan — Thu, 19 Apr 2007 12:26:10 GMT

what happend?

Re: update table

TwoProc — Thu, 19 Apr 2007 12:29:01 GMT

What error do you get?

Re: update table

Ratzie — Thu, 19 Apr 2007 12:29:07 GMT

same error
I have never used double quotes around values

Re: update table

Ratzie — Thu, 19 Apr 2007 12:31:31 GMT

invalid sql statement

Re: update table

Aussan — Thu, 19 Apr 2007 12:32:19 GMT

what's the error??

Re: update table

Aussan — Thu, 19 Apr 2007 12:36:09 GMT

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

Re: update table

Ratzie — Thu, 19 Apr 2007 12:36:12 GMT

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

Re: update table

TwoProc — Thu, 19 Apr 2007 12:39:31 GMT

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

Re: update table

TwoProc — Thu, 19 Apr 2007 12:42:03 GMT

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

(oops)

Re: update table

Hein van den Heuvel — Thu, 19 Apr 2007 13:14:15 GMT

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

Re: update table

TwoProc — Thu, 19 Apr 2007 14:00:21 GMT

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.

Re: update table

Yogeeraj_1 — Fri, 20 Apr 2007 05:39:43 GMT

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

Re: update table

Ratzie — Fri, 20 Apr 2007 08:52:25 GMT

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