Re: This is not trivia pursuit....

[Sam Spritzer <SSpritzer@GW.CTG.COM>]

Thanks Raj and to those who respond but this solution did work.

Even though I had b.emplid = a.emplid in the sub-select, I also had to consider the possibility of matching, or at least, checking for the existence of emplids in the main select.  EMPLOYMENT is populated with everyone whereas, EMPLOYEES is only a selected group.

Thanks again!
Sam


<<< <Rajiv_Agrawal@BC.COM>  1/ 6  5:16p >>>
Sam

Try the following statement:

        SQL> update ps_employment a
           2    set       a.business_title = (select b.jobtitle
           3                                            from ps_employees b
           4                                            where a.emplid =
b.emplid)
           5.where exists ( select 'x' from ps_employee b1
                                   where b1.emplid = a.empid) ;

HTH
--------------------
Rajiv


> -----Original Message-----
> From: Sam Spritzer [SMTP:SSpritzer@GW.CTG.COM]
> Sent: Thursday, January 06, 2000 2:48 PM
> To:   SQR-USERS@list.iex.net
> Subject:      This is not trivia pursuit....
>
> Don't you just love it when clueless users make a request that, in their
> world, makes perfect sense to them within the realm of their logic and
> common
> sense.....hence, take a look at the following.
>
> I am trying to update a varchar(30) column from one table with a
> varchar(30) column from another table. I have already tested both columns
> for null
> values....and there aren't any. Can any of you shed any light in this?
>
>             *********************************************
> SQL*Plus: Release 3.3.2.0.2 - Production on Thu Jan 06 16:26:11 2000
>
> Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.
>
>
> Connected to:
> Oracle7 Server Release 7.3.4.3.0 - Production
> With the distributed, replication, parallel query and Spatial Data options
> PL/SQL Release 2.3.4.3.0 - Production
>
> SQL> update ps_employment a
>    2 set a.business_title = (select b.jobtitle
>    3 from ps_employees b
>    4 where a.emplid = b.emplid);
> from ps_employees b
>      *
> ERROR at line 3:
> ORA-01407: cannot update mandatory (NOT NULL) column to NULL
>             ****************************************
>
> SQRly yours,
> Sam