Self-Update of Table Value(s) - Efficiency

From: GHouck <hksys_at_teleport.com>
Date: 2000/04/24
Message-ID: <3905333C.454A@teleport.com>#1/1

I have had to update columns within a table from other rows within the same table, based on one or more other columns' values (from the same table).

Given my methodology (and its problems), can someone suggest a different (1-more efficient, 2-not prone to setting un-matched columns to NULLs, and 3-less cumbersome)?

Let's say that I want to update columns A,B, and C based on a match within the same table between columns D, E, and F. This is what I might try:

update mytbl OO set (OO.A,OO.B,OO.C) =
(

 select II.A,II.B,II.C from mytbl II
 where

   II.D = OO.D and 
   II.E = OO.E and 
   II.F = OO.F and
   II.rowid <> OO.rowid

)

Let's also assume for this case that the query only returns a single row (that's another problem).

Is this the best way to do it? How does one prevent non-matches from NULL'ing out already existing values for OO.A,OO.B,OO.C if no match is found?

What I've tried (which seems cumbersome) is:

update mytbl OO set (OO.A,OO.B,OO.C) =
(

 select II.A,II.B,II.C from mytbl II
 where

   II.D = OO.D and 
   II.E = OO.E and 
   II.F = OO.F and
   II.rowid <> OO.rowid

)
where exists
(

 select II.A,II.B,II.C from mytbl II
 where

   II.D = OO.D and 
   II.E = OO.E and 
   II.F = OO.F and
   II.rowid <> OO.rowid

)

It works, but there has to be a better
way than to repeat the whole query.

Thanks,

Geoff Houck
systems hk
hksys_at_teleport.com
http://www.teleport.com/~hksys Received on Mon Apr 24 2000 - 00:00:00 CDT