Re: This is not trivia pursuit....

[Greg Hesla <ghesla@CIMACONSULTING.COM>]

I should have suggested a fix rather than just pointing out what was
wrong...
Anyway, here's what I'd do...

update ps_employment a
set a.business_title = (select b.jobtitle
from ps_employees b
where a.emplid = b.emplid)
where exists (select 'x'
from ps_employees
where emplid = a.emplid);

================================
Greg Hesla
ghesla@cimaconsulting.com
Cima Consulting Group
www.cimaconsulting.com
================================


----- Original Message -----
From: "Sam Spritzer" <SSpritzer@GW.CTG.COM>
To: <SQR-USERS@list.iex.net>
Sent: Thursday, January 06, 2000 4:48 PM
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
>