Re: Sub-select returning nulls

I cannot test this as I do not have access to Oracle at work, but..

UPDATE table1
SET column1 = (SELECT NVL(column2,column1)

                 FROM table2, table1
                WHERE table1.columnA (+) = table2.columnB)

or if UPDATE can take list of tables then

UPDATE table1, table2
SET table1.column1 = table2.column2
WHERE table1.columnA = table2.columnB

which, if this is legal syntax, gets rid of the outer join.

In article <3821fb51.415619_at_news1.a2000.nl>,   jantah_at_big.hand.com (Jantah) wrote:
> 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)
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 04 1999 - 22:08:18 CST