Re: SQL question:

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Look at your statement, ask yourself what value does a.status get if
there isn't a corresponding value in locnmstrpf.

The asnwer is NULL.

If a.status doesn't allow NULL, then you get the error you are seeing.

Two options,
1) Continue to update all records in drvrmstrpf but update records
without match in locnmstrpf with a default value or with the existing
value.
2) Change the statement to only update records in drvrmstrpf that have
a match in locnmstrpf

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

I'll leave option 1 as an exercise for you. :)  Here's a hint, you'll
probably want to use coalesce().

HTH,
Charles



On Thu, Feb 18, 2010 at 10:45 AM, Tom <tomh@xxxxxxxxxxx> wrote:
>   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?
>
>
>
>
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.