>Good start, thanks Borislav
>
>>>Hi all,
>>>Customers buy a licence for a product every year. Details are logged in the table TableA below.
>>>
>>>What I would like to achieve with an update sql is this:
>>>
>>>I need help to craft an sql statement that will flag the ones that have expired. If a customer has never renewed the contract (ie there is only 1 record of cusomer/product in the table) that contract stays. If there is more than 1 record for customer and product then those with ExpiryDate less that today should be flagged expired. However if all the customer’s product licences have expired (however many) the most recent of the lot should be left as active.
>>>
>>>
>>>
>>>TableA before update sql
>>>nKey cCustomerID cProductID ExpiryDate Expired
>>>1 Cust001 Prod001 01-01-2007 No
>>>2 Cust001 Prod001 01-01-2008 No
>>>3 Cust001 Prod001 01-01-2009 No
>>>4 Cust001 Prod002 01-01-2008 No
>>>5 Cust001 Prod002 01-01-2009 No
>>>6 Cust002 Prod002 01-01-2009 No
>>>7 Cust003 Prod003 01-01-2007 No
>>>8 Cust003 Prod002 01-01-2009 No
>>>9 Cust003 Prod003 01-01-2008 No
>>>10 Cust004 Prod001 01-01-2007 No
>>>
>>>TableA after update sql
>>>nKey cCustomerID cProductID ExpiryDate Expired
>>>1 Cust001 Prod001 01-01-2007 Yes
>>>2 Cust001 Prod001 01-01-2008 Yes
>>>3 Cust001 Prod001 01-01-2009 No
>>>4 Cust001 Prod002 01-01-2008 Yes
>>>5 Cust001 Prod002 01-01-2009 No
>>>6 Cust002 Prod002 01-01-2009 No
>>>7 Cust003 Prod003 01-01-2007 Yes
>>>8 Cust003 Prod002 01-01-2009 No
>>>9 Cust003 Prod003 01-01-2008 No
>>>10 Cust004 Prod001 01-01-2007 No
>>>
>>>Thanks in advance
>>
>>
>>How about:
>>

>>UPDATE TableA SET Expired = CASE WHEN ExpiryDate < TblB.ExpiryDate
>>                                      THEN 'Yes'
>>                                 ELSE 'No' END
>>FROM TableA
>>INNER JOIN (SELECT cCustomerID, cProductID, MAX(ExpiryDate) AS ExpiryDate
>>                   FROM TableA
>>            GROUP BY  cCustomerID, cProductID) TblB
>>     ON TableA.cCustomerID = TblB.cCustomerID  AND
>>        TableA.cProductID  = TblB.cProductID
>>

>>
>>NOT TESTED!!!

You could try also this:

UPDATE TableA SET Expired = 'Yes'
FROM TableA
INNER JOIN (SELECT cCustomerID, cProductID, MAX(ExpiryDate) AS ExpiryDate
                   FROM TableA
            GROUP BY  cCustomerID, cProductID) TblB
     ON TableA.cCustomerID = TblB.cCustomerID  AND
        TableA.cProductID  = TblB.cProductID   AND
        TableA.Expired     < TblB.ExpiryDate

But be sure you have a real good backup first :-)