Update 1 record in TBL1 when all references in TBL2 are equal to a given value

Question

Hi,

I have two tables, 1 indexing the details of an email campaign. The second table details the recipients of this campaign, and whether they have responded to the email etc.

I need to create a stored procedure that can update the status of the 'master record' in TBL1 when all the references(recipients) in TBL2 have a status >1.

The table structure for simplicity can be assumed to be;

TBL1

key | Title | Status(default 1)


TBL2

Key | TBL1_Key | Recipient | Status(default 0)

So essentially what i need to do is set the status of each record(max 100 per call) in TBL1 (where its status is already == 1) to '2' when all corresponding records in TBL2 have a status of > 1.

The records are linked ON TBL1.key = TBL2.TBL1_key

Hope this is explained clearly, and that you can offer me some help.

As always.... THANKS!!!!

Answer 1

UPDATE   tbl1 t1
SET      status = 2
WHERE    status = 1
AND      NOT EXISTS
         (
         SELECT  NULL
         FROM    tbl2 t2
         WHERE   t2.key = t1.key
                 AND t2.status <= 1
         )

Answer 2

If I understand your requirement correctly, then the Status in TBL1 can be derived from the Status values in TBL2. So the Status in TBL1 is redundant and could be omitted. Instead you could define a view that lists all the entries from TBL1 and includes a further column where you calculate the status based on the entries on TBL2. You could e.g. count the number of entries with Status < 2 in TBL2 and return overall-Status 2 when this number is 0 etc.