Re: SQL connundrum. Can it be done?

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

• Create a collection consisting of a record of nested tables. You will need to create a nested table for your new value for "id3", and one for the primary key of syz_table.
• Bulk insert the values into your collection, with LIMIT 1000. For records of nested tables, you bulk insert into INDIVIDUAL elements of the collection, not into the whole collection at once.
• Bulk update ("FORALL") your table. Use the primary key (stored in the collection) to find individual records in xyz_table.

    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