- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: update table
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-19-2007 05:36 AM
тАО04-19-2007 05:36 AM
Re: update table
ORA-00904 invalid column name.
But, the columns are correct!
It is complaining on the master_key columns
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-19-2007 05:39 AM
тАО04-19-2007 05:39 AM
SolutionThis 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')
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-19-2007 05:42 AM
тАО04-19-2007 05:42 AM
Re: update table
"update table1"
not
"update tab1e1" at the statement beginning of course...
(oops)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-19-2007 06:14 AM
тАО04-19-2007 06:14 AM
Re: update table
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-19-2007 07:00 AM
тАО04-19-2007 07:00 AM
Re: update table
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-19-2007 10:39 PM
тАО04-19-2007 10:39 PM
Re: update table
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-20-2007 01:52 AM
тАО04-20-2007 01:52 AM
Re: update table
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
- « Previous
-
- 1
- 2
- Next »