USP_EMAIL_SOFT_DELETE

create procedure dbo.USP_EMAIL_SOFT_DELETE
(
@MINDATE datetime
)
as

begin
    set nocount on;

 update email set  deleted =1 where id in  (
        select child.ID  as ID from dbo.Email child 
      inner join dbo.Email parent on child.ParentEmailID = parent.ID where
      parent.Deleted = 0 and parent.SendAfterDate < @MINDATE
      union

      -- get the parent

      select ID from dbo.email where Deleted = 0 and SendAfterDate < @MINDATE
    )
end