SQL server auto update column
You need to update the defaults column by using an insert trigger on this table. The only problem with it would be that your trigger will fail on multirow-insert.
There is a correction in the WHERE clause you are using. Following is the code you should use:

UPDATE FYPDB.dbo.Promotions 
SET defaults = 'N' 
WHERE ID NOT IN (SELECT ID FROM Inserted) AND membershipType IN (SELECT membershipType FROM Inserted) AND EXISTS (SELECT * FROM Inserted WHERE defaults = 'Y'); 
UPDATE FYPDB.dbo.Promotions 
SET defaults = 'Y' 
WHERE ID IN (SELECT ID FROM Inserted) AND NOT EXISTS (SELECT * FROM FYPDB.dbo.Promotions p WHERE p.defaults = 'Y' and p.membershipType in (SELECT membershipType FROM Inserted));