Re: Oracle SQL - Update

jwkreul_at_vms.macc.wisc.edu (John W. Kreul, Hosp Info Svcs) writes:

> Oracle SQL Question - Update Command
 

> I am trying to update one Oracle table using data from another table. The
> initial statement that I constructed is below.
 

> When I run the statement it returns the Oracle Error -
> (a.street1,
> *
> ERROR at line 2:
> ORA-01407: cannot update mandatory (NOT NULL) column to NULL
 

> All the fields in both tables are set to NOT NULL and contain data.
 

> I would appreciate any help on this manner.

You need to add a little something to the end of your current SQL Update statement in order to not try to update those rows that are unupdatable (is there such a word :-). Rows that are unuptatable are those that have no corresponding rows in your ps_employee_update table.

> update ps_personal_data a set
> (a.street1, a.street2, a.city, a.state, a.country, a.zip, a.work_phone,
> a.work_phone2, a.location, a.location_room, a.home_phone, a.email_part1,
> a.email_part2) =
> (select
> b.street1, b.street2, b.city, b.state, b.country, b.zip, b.work_phone,
> b.work_phone2, b.location, b.location_room, b.home_phone, b.email_part1,
> b.email_part2
> from ps_employee_update b where b.ssn = a.ssn)
where exists (select null from ps_employee_update where ssn = a.ssn);

I presume that the column ssn, in both tables, is the primary key; if not, then there is the danger that you might have Two records in the ps_employee_update table with the same ssn, in which case the update statement above would fail (you'd be trying to update a single record from the ps_personal_data table with Two records from the ps_employee_update table).

> Thank you.

You're welcome.

Hank Robinson
Oracle DBA
University of Warwick Received on Fri Sep 16 1994 - 12:48:47 CEST