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: SQL connundrum. Can it be done?

Re: SQL connundrum. Can it be done?

From: Daniel Roy <danielroy10junk_at_hotmail.com>
Date: 31 Jul 2003 07:51:21 -0700
Message-ID: <3722db.0307310651.29ccfa77@posting.google.com>


I would resolve your issue with PL/SQL if I were you. You can make it VERY efficient if you follow these guidelines:

    For questions on the syntax, look at the chapter on collections in the PL/SQL manual at tahiti.oracle.com. For examples, do a search on asktom.oracle.com, or email me (remove the "junk" from my username to get my email address).

HTH Daniel

> I need to update a table with the output of a select statement like I listed
> below. The problem is that I expect my select to have about 7,000,000 rows
> returned so I want to update only one column in the table for all 7,000,000
> rows. I was told you can't do it easily with SQL. I would either need PL/SQL or
> use SQL to generate 7,000,000 seperate update statements. Not efficient enough
> for me.
> Sooooo... my challenge to you is, can I accomplish my multi-row update with
> standard old SQL statements? Thanks. To complicate matters one of my tables in
> the join is actually a select statement inline view.
>
> update xyz_table x
> set x.id3 =
> (select t.id3
> from xyz_table x,
> (select distinct id1, id2 from xyz_tmp_table where criteria_id='9998') t
> where x.criteria_id ='9999' and <--- This is my subselect where clause
> x.id1 = t.id1 and
> x.id2 = t.id2 and
> x.id3 is null)
> where x.criteria_id = '9999' and <--- This is my update where clause
> x.id1 = t.id1 and
> x.id2 = t.id2 and
> x.id3 is null;
Received on Thu Jul 31 2003 - 09:51:21 CDT

Original text of this message

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