At 10:24 PM 04-06-02 -0400, you wrote:

>Hi:
>
> Update query below was run on 6/3/2002 and inserted invoice date
>for a member whose status was cancelled but whose record had a
>valid renew_date entry.
>
> It did not seem to exclude the M2 record even though the M record
>had neither ACTIVE or ASSOCIATE as the status.
>
> What is really strange though (and here is the rub) it nulled
>the ID field in M2 where M Status was CANCELLED.
>
> Now this has not posed a problem since the lack of an ID prevents
>the invoice from being printed but leaves an orphaned record
>in the database (several). Unfortunately this record contains other
>information which is not necessarily to be discarded since a cancelled
>member can be reinstated.
>
> Right now I am going back and manually putting the
>correct ID in the orphaned records. Not a good op
>for my "normal" users.
>
> The screw up is obviously at the <----- below where it
>sets the ID in M2 = null since it can't find an M.ID in a record
>meeting ACTIVE or ASSOCIATE.

Yes, that's where the screw-up is. You should not be updating M2.ID at
all. You want an EXISTS() subselect to establish the matches between M2.ID
and M.ID. If it finds any matches that satisfy the criteria in the
subselect, you want it to update REMIND_LET on the M2 records, otherwise
ignore them. In no case is it valid to change the M2.ID value.

The way you have it here, M2.ID will be set to null on every M2 record
where the update conditions are not met.

You could try this:

UPDATE MEMPAY2 M3
SET M3.REMIND_LET = :InvoiceDate
WHERE (EXISTS (SELECT 1 FROM MEMPAY2 M2
JOIN MEMBERS M ON M2.ID = M.ID
AND M2.RENEW_DATE = M.RENEW_DTE
WHERE M.ID = M3.ID
AND (M.STATUS_MEM = 'ACTIVE' OR M.STATUS_MEM = 'ASSOCIATE')
AND M2.DATE_PAID IS NULL AND
AND M2.REMIND_LET IS NULL
AND M2.RENEW_DATE IS NOT NULL
AND (M2.RENEW_DATE >= '05/04/2002' AND M2.RENEW_DATE <= '07/18/2002')))

...otherwise I'd want to go the safe route and do this in a stored procedure.

heLen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________