Sub-select returning nulls

I have an update query that looks something like this:

UPDATE table1
SET column1 =

(SELECT column2

       FROM   table2
       WHERE  table1.columnA = table2.columnB)

This returns an error "cannot update mandatory column to NULL", which makes sense, as the sub-select does not return a value for every record in table1.

So I tried:

UPDATE table1
SET column1 =

(SELECT column2

       FROM   table2
       WHERE  table1.columnA = table2.columnB)
WHERE  EXISTS


      (SELECT *


       FROM   table2
       WHERE  table1.columnA = table2.columnB)

This works, but the second sub-select looks a bit redundant, and the query will access table2 twice. Also, I don't like this from a maintainance point of view, because the two sub-selects must match for the update to work correctly.

Only other option I can think of is:

UPDATE (SELECT column1,

               column2
        FROM   table1,
               table2
        WHERE  table1.columnA = table2.columnB)
SET     column1 = column2

but in my case this returns the error "cannot update a column that maps to a non key-preserved table", which is correct, but I cannot change any primary keys.

So I am stuck with option 2, an ugly query that accesses a table twice.

Any suggestions anybody??

(Using Oracle 7.3.3)
--
Jan
jantah_at_big.hand.com
(replace big.hand with bigfoot to email Jan) Received on Thu Nov 04 1999 - 15:53:09 CST