Returning an updated column value based on the conditions of another column value in SQL Server

Question

I have a table

Referrals

with these columns:

ref_id

referrer_id(same as profile id)

referrer_bonus_amount

referral_valid

valid_from

valid_till
.

I need to write code to extend the

valid_till

date for a month of tenants who have referred more than two times. The number of references made can calculated by the number of occurrences of a particular number (

profile_id

) in the column

referrer_id

(same as profile id). The below two pieces of code are doing their job individually but I don't know how to join them to get the desired result.

select DATEADD(mm, 1, valid_till)

from Referrals



select 

    [referrer_id(same as profile id)], 

    count([referrer_id(same as profile id)]) 

from referrals

group by [referrer_id(same as profile id)]

having count([referrer_id(same as profile id)]) > 2

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

You could use the in operator:

SELECT DATEADD(MM, 1, valid_till) 
FROM   referrals
WHERE  referrer_id IN (SELECT   referrer_id
                       FROM     referrals
                       GROUP BY referrer_id
                       HAVING   COUNT(*) > 2)

Or if you really need to update the table and not just query the added month, you could use the same idea in an update statement:

UPDATE referrals 
SET    valid_till = DATEADD(MM, 1, valid_till) 
WHERE  referrer_id IN (SELECT   referrer_id
                       FROM     referrals
                       GROUP BY referrer_id
                       HAVING   COUNT(*) > 2)

Returning an updated column value based on the conditions of another column value in SQL Server

Sign up