Re: 'mandatory not null' dilemma

From: Gerard M. Averill <e-mail.address_at_my.sig>
Date: Thu, 27 May 99 17:15:37 GMT
Message-ID: <7ijua8$ule$1@news.doit.wisc.edu>

In article <7ieu2c$bgb$1_at_nnrp1.deja.com>, damon_short_at_aon.com wrote:
>This was addressed awhile back, but I still don't understand why the
>following won't work:
>
>UPDATE table_2
>SET (column_1,column_2)=
>(SELECT column_1,column_2 from table_1
>where table_1.column_1=table_2.column_1
> and table_2.column_2=table_2.column_2)
>
>These are 'not null' fields, but NONE OF THE ROWS IN table_1 are NULL.
>
>yet I get:
>ORA-01407: cannot update mandatory (NOT NULL) column to NULL
>

From the UPDATE statement documentation:

"If the SET clause contains a subquery, it must return exactly one row for each row updated. Each value in the subquery result is assigned respectively to the columns in the parenthesized
list. If the subquery returns no rows, then the column is assigned a null. Subqueries may select from the table being updated."

Thus, there are (table_2.column_1, table_2.column_2) pairs which don't have a match in table_1; therefore your subquery is returning null values for its two columns. To skip these pairs, simply add a WHERE clause to the UPDATE statement as such:

UPDATE table_2
SET (column_1, column_2)=

(SELECT column_1, column_2 from table_1
where table_1.column_1=table_2.column_1
  and table_1.column_2=table_2.column_2)

WHERE exists

(SELECT * from table_1
where table_1.column_1=table_2.column_1
  and table_1.column_2=table_2.column_2)

Hope this helps,
Gerard

---

Gerard M. Averill, Researcher
CHSRA, University of Wisconsin - Madison GAverill<at>chsra<dot>wisc<dot>edu Received on Thu May 27 1999 - 12:15:37 CDT