Operating System - HP-UX
1748201 Members
2882 Online
108759 Solutions
New Discussion юеВ

Re: Query Update from in Oracle

 
SOLVED
Go to solution
Petr Simik_1
Valued Contributor

Query Update from in Oracle

I use in MS SQL nice tricks in joining tables. It is based on (update from)
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.
4 REPLIES 4
Nicolas Dumeige
Esteemed Contributor
Solution

Re: Query Update from in Oracle

Hello,

If 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
All different, all Unix
Petr Simik_1
Valued Contributor

Re: Query Update from in Oracle

Than you this is the syntax I was looking for.

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
Nicolas Dumeige
Esteemed Contributor

Re: Query Update from in Oracle

To speed things up, would'nt be possible to do just 1 UPDATE using decode().

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
) ;
All different, all Unix
Yogeeraj_1
Honored Contributor

Re: Query Update from in Oracle

hi,

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 ) loop
update table1
set field1 = rc1.field1
where ;
end loop;
commit;
end;

hope this helps too!

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)