 |
update several rows from a select???
update tbl r set (c1, c2, c3,c4, c5, c6, c7)= ( SELECT distinct c1, c2, c3,c4, c5, c6, c7 from tbl i where i.c8= r.c8 and i.c7> r.c7 ) i am trying to update several rows from a select this rows can be up
to 500k.
this is a huge time consuming for this.
Is it a better way of doing it???
Thanks in advance
A
"valigula" <valig@gmail.com> a crit dans le message de news: 1178553186.644822.101@y5g2000hsa.googlegroups.com...
| update tbl r set
| (c1, c2, c3,c4,
| c5, c6, c7)=
| ( SELECT distinct c1, c2, c3,c4,
| c5, c6, c7
| from tbl i
| where i.c8= r.c8
| and i.c7> r.c7
| )
|
|
| i am trying to update several rows from a select this rows can be up
| to 500k.
| this is a huge time consuming for this.
|
| Is it a better way of doing it???
|
| Thanks in advance
|
| A
|
As we answer one hour ago in c.d.o.m: use MERGE command.
And don't multipost but you can reply in the original if our
answer doesn't fit your requirement. Just tell why.
And POST YOUR ORACLE VERSION as Daniel already
asked you.
Regards
Michel Cadot
-----------------------------------------------Reply-----------------------------------------------
On 7 mayo, 17:56, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "valigula" <valig @gmail.com> a crit dans le message de news: 1178553186.644822.101 @y5g2000hsa.googlegroups.com... > | update tbl r set > | (c1, c2, c3,c4, > | c5, c6, c7)= > | ( SELECT distinct c1, c2, c3,c4, > | c5, c6, c7 > | from tbl i > | where i.c8= r.c8 > | and i.c7> r.c7 > | ) > | > | > | i am trying to update several rows from a select this rows can be up > | to 500k. > | this is a huge time consuming for this. > | > | Is it a better way of doing it??? > | > | Thanks in advance > | > | A > | > As we answer one hour ago in c.d.o.m: use MERGE command.
> And don't multipost but you can reply in the original if our
> answer doesn't fit your requirement. Just tell why.
> And POST YOUR ORACLE VERSION as Daniel already
> asked you.
> Regards
> Michel Cadot
oopps ... sorry for posting twice i just check the answer and that siuts perfect what i need .... ty
-----------------------------------------------Reply-----------------------------------------------
On 7 mayo, 19:56, valigula <valig @gmail.com> wrote:
> On 7 mayo, 17:56, "Michel Cadot" <micadot{at}altern{dot}org> wrote: > > "valigula" <valig@gmail.com> a crit dans le message de news: 1178553186.644822.101@y5g2000hsa.googlegroups.com...
> > | update tbl r set
> > | (c1, c2, c3,c4,
> > | c5, c6, c7)=
> > | ( SELECT distinct c1, c2, c3,c4,
> > | c5, c6, c7
> > | from tbl i
> > | where i.c8= r.c8
> > | and i.c7> r.c7
> > | )
> > |
> > |
> > | i am trying to update several rows from a select this rows can be up
> > | to 500k.
> > | this is a huge time consuming for this.
> > |
> > | Is it a better way of doing it???
> > |
> > | Thanks in advance
> > |
> > | A
> > |
> > As we answer one hour ago in c.d.o.m: use MERGE command.
> > And don't multipost but you can reply in the original if our
> > answer doesn't fit your requirement. Just tell why.
> > And POST YOUR ORACLE VERSION as Daniel already
> > asked you.
> > Regards
> > Michel Cadot
> oopps ... sorry for posting twice i just check the answer and that
> siuts perfect what i need ....
> ty- Ocultar texto de la cita -
> - Mostrar texto de la cita -
thanks but i have one more question (on oracle 9i). table1 has no pk but table2 the problem using the the merge ... because of the pk MERGE INTO table2 r
USING (
SELECT *
from table1
) i
ON (r.c1 = i.c1)
WHEN MATCHED THEN
update set r.c2='2'
, r.c3 =i.c3
, r.c4 = i.c4
, r.c5 = i.c5
, r.c6 = i.c6
, r.c7 = i.c7
, r.c8=i.c8
WHEN NOT MATCHED THEN
INSERT
(r.c1, r.c2, r.c3, r.c4, r.c5,
r.c6, r.c7, r.c8) values ( '2' , i.c2, i.c3, i.c4, i.c5,
i.c6,
i.c7, i.c8)
any ideas thanks
|
 |
Add to del.icio.us |
Digg this |
Stumble it |
Powered by Megasolutions Inc
