Re: SQL connundrum. Can it be done?

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: 31 Jul 2003 05:13:39 -0700
Message-ID: <1a75df45.0307310413.65659349@posting.google.com>

rugby17043_at_aol.com (Rugby17043) wrote in

> 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.

Also does not sound like real advice to 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;

The SQL does not seem right. The inline view is doing a distinct select on id1 and id2 (called t), and yet you want to set xyz.id3 equal to t.id3. You did not define a t.id3.

t.id3 is not part of the inline view's projection. Also, why the distict select on t.id1 and t.id2 then?

Looking at the UPDATE sql I could only draw the following conclusion: You want to update id3 in xyz where criteria = '9999' and id3 is null

What I do not understand is where you want to find the value for id3. Is that from the _same_ table but where criteria is '9998' and (id1,id2) matches?

If so, then I'm thinking of something simple like: UPDATE xyz x
  SET x.id3 =
    (SELECT

       t.id3
     FROM xyz t 
     WHERE t.criteria = '9998'
     AND   t.id1 = x.id1
     AND   t.id2 = x.id2

    )
WHERE x.criteria = '9999'
AND x.id3 IS NULL

Of course, this will not work if (criteria,id1,id2) is not unique and the nested SQL returns multiple rows.

The UPDATE I did above, should be "acceptable" if (criteria,id1,id2) is unique and indexed. That means a single unique index scan per row to update. For 7 million rows to update, that means 7 million unique scans.. but the invidual SQL looking for the correct/non-null id3 value can not go any faster than that in this situation. Unless...

The mutiple row problem could explain why you refer to a xyz_temp table in your update..?

If so, then something like this should work: CREATE TABLE xyz_tmp
PCTFREE 0
CACHE
NOLOGGING AS
SELECT id1, id2, MAX(id3) AS id3 FROM xyz WHERE criteria = '9998'
GROUP BY id1, id2

Create a unique index for xyz_tmp on (id1,id2). Then try this update: UPDATE xyz x
  SET x.id3 =
    (SELECT

       t.id3
     FROM xyz_tmp t 
     WHERE t.id1 = x.id1
     AND   t.id2 = x.id2

    )
WHERE x.criteria = '9998'
AND x.id3 IS NULL

Drop xyz_tmp.

But I'm guessing here.. you should try and explain what you are attempting with a bit more detail.

--
Billy

Received on Thu Jul 31 2003 - 07:13:39 CDT