Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: update several rows from a select???

Re: update several rows from a select???

From: valigula <valigula_at_gmail.com>
Date: 7 May 2007 12:05:16 -0700
Message-ID: <1178564716.092895.220060@y80g2000hsf.googlegroups.com>


On 7 mayo, 19:56, valigula <valig..._at_gmail.com> wrote:
> On 7 mayo, 17:56, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
>
>
>
>
>
> > "valigula" <valig..._at_gmail.com> a écrit dans le message de news: 1178553186.644822.101..._at_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 Received on Mon May 07 2007 - 14:05:16 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US