RE: SQL question:

["Dan Kimmel" <dkimmel@xxxxxxxxxxxxxxx>]

Try this:

Update drvrmstrpf a                     
Set a.status=                                        
    (select 'C'  from locnmstrpf b 
     Where a.locn = b.locn and b.prdend=20100211)
where exists  
    (select 'C'  from locnmstrpf b 
     Where a.locn = b.locn and b.prdend=20100211)


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Tom
Sent: Thursday, February 18, 2010 9:46 AM
To: 'Midrange Systems Technical Discussion'
Subject: SQL question:

I have 2 tables:  DRVRMSTRPF, LOCNMSTRPF.

 

DRVRMSTRPF fields: LOCN, DRVR, STATUS

LOCNMSTRPF fields: LOCN, PRDEND, STATUS

 

All drvrmstrpf.locn values are found in locnmstrpf, but there may be
some locnmstrpf entries for which there are no corresponding entries in
drvrmstrpf.

 

I have a request from a user:  update the drvrmstrpf status code to 'C'
for all locnmstrpf records with PRDEND value of 20100211.

 

I've tried the following SQL, but getting an error ("Null values not
allowed in column or variable STATUS"):

 

Update drvrmstrpf a                     

Set a.status=                                        

    (select 'C'  from locnmstrpf b 

     Where a.locn = b.locn and b.prdend=20100211)

 

What am I missing?