Re: SQL UPDATE HELP or consider it a Challenge.

rugby17043_at_aol.com (Rugby17043) wrote in message news:<20030730233958.28155.00000583_at_mb-m24.aol.com>...
> 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;

Updating 7 million rows in one SQL statement is not a problem as long as 1- the time necessary for the statement to complete is not too long to lock out other update intent users, 2- your system has enough rollback segment to hold the table and associated index data (if any) for the transaction, and 3- there are not so many other updaters running against the table at the same time so that your session does not run into lock waits which in turn would cause your task to violate condition 1.

By the way since you are updating id3 and have a where clause condition on id3 such that the update of the column causes the row to fall out of the result set you could easily add a "and rownun < 100000" or other manageable number of rows to your update, run it, commit, and repeat till the entire table has been processed should one of the above conditions present a problem.

HTH -- Mark D Powell -- Received on Thu Jul 31 2003 - 07:57:11 CDT