- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Query Update from in Oracle
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
тАО07-27-2004 11:00 PM
тАО07-27-2004 11:00 PM
like this below:
update table1
set table1.column=table2.xx
from
table1,table2 where ...join
...
from table1
inner join table2 on table1.xx=table2.yy
do you KNOW HOW to write this syntax IN ORACLE??
I didn't find (update from) syntax in Oracle, but is there any other way how to do that.
Reason for that is to keep 100% sure one table consistent with same numbers of rows and unchanged data. In case when I have two/more tables with different numbers of rows it helps me to join without changing number of rows and without preparing many temp tables in case when I do that for more tables.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-28-2004 12:28 AM
тАО07-28-2004 12:28 AM
SolutionIf you use an UPDATE statement, there will be no row creation, you can be sure of that ;-D
Could you tell in english what you're trying to do ? I'm not sure I understand what you're aiming at ...
Anyway, if you're looking for ways of updating a table using join, here's two examples :
UPDATE
table_1 a
SET
a.field = (
SELECT b.field2
FROM tables2 b
WHERE a.field3 = b.field4
) ;
UPDATE
(
SELECT
tGLrst.SSENSTR,
tPCIrst.SENSTR,
tGLrst.SCODMNEVAF,
tPCIrst.CPT_NAT,
tGLrst.SIND_EXLCVA,
tPCIrst.IND_EXLCVA,
tGLrst.SAXEOBJ,
tPCIrst.AXEOBJ,
tGLrst.SAXETIE,
tPCIrst.AXETIE
FROM
tGLrst, tPCIrst
WHERE
tGLrst.CPT_PCR = tPCIrst.NUMPCI
)
SET
SSENSTR = SENSTR,
SCODMNEVAF = CPT_NAT,
SIND_EXLCVA = IND_EXLCVA,
SAXEOBJ = AXEOBJ,
SAXETIE = AXETIE
;
Cheers
Nicolas
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-28-2004 12:46 AM
тАО07-28-2004 12:46 AM
Re: Query Update from in Oracle
update
table1 a
set
a.t1_column= (select b.t2column
from
table2 b
where
a.t1key=b.t2key)
Answer to your question why I am doing that:
I use this trick to avoid to create many TEMP tables and to have outerjoin without changing number of rows.
Steps of my doing are:
1.create table1 where I have to mark some specific rows
2.creace columns into table1 as indicators of specific rows
3.perform update from (thanks for your HELP here)
4.repeat steps (2,3) unless I have what I want
5. This is my report.
Thank you
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-28-2004 01:32 AM
тАО07-28-2004 01:32 AM
Re: Query Update from in Oracle
If you want to update a field with different values depending on the values of another field, try this :
UPDATE table_1 a
SET a.field = (
SELECT decode(b.field2, 'if_value','then_value' , [...], 'default_value')
FROM tables2 b
WHERE a.field3 = b.field4
) ;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-28-2004 01:55 AM
тАО07-28-2004 01:55 AM
Re: Query Update from in Oracle
note that you can also use syntax as follows:
create table table1 as
select fld1, fld2,...
from table2
where
to create your table.
and to process your records you may also use loops (PL/SQL) as:
begin
for c1 in (select * from table2 where
update table1
set field1 = rc1.field1
where
end loop;
commit;
end;
hope this helps too!
regards
Yogeeraj