title: Using an if statement to check if a record doesn't exist not working in sqlserver
body: <p>I am trying to check if a record doesn't exist in an other table. It must do an insert and perform an update only on that entry and not on any of the existing items in that table.
  This is what I tried:</p>
  
  <pre><code>IF NOT exists (SELECT *
                      FROM dbo.Log  s inner join #results r on s.Number = r.Number)
     BEGIN
         insert into  //if it doesn't exist enter the row dbo.Log(Number,FirstName,LastName,Mobile,MessageContent,MessageStatus)
         select Number
               ,FirstName
               ,LastName
               ,MobileNumber
               ,MessageContent
               ,MessageStatus
         from #results r
   update r //then only update that row that was inserted
      set TextNumb = v.TextNumber
      from 
      (select r.*, row_number() over (order by newid()) as seqnum
        from [dbo].[Log] r where MessageStatus=0
       ) r join
       (select v.*, row_number() over (order by newid()) as seqnum
        from dbo.TextNUMBERS v
        where Flag=0
       ) v
       on r.seqnum = v.seqnum;
       update dbo.TextNUMBERS
              set flag =1
       from  [dbo].[Log] re 
   where re.TextNumb=dbo.TextNumbers.TextNumber
   END 
  </code></pre>
  
  <p>So in the above I tried that if the record doesn't exist then insert and update only that record, but it's not working, it's only inserting the first time when I run the query with no values in the other table but if I run the query the second time it doesn't insert any new record that comes in after that.</p>
  
  <p>I also tried:</p>
  
  <pre><code>insert into 
      dbo.Log(Number,FirstName,LastName,Mobile,MessageContent,MessageStatus)
             select Number
                   ,FirstName
                   ,LastName
                   ,MobileNumber
                   ,MessageContent
                   ,MessageStatus
             from #results r
  where not exists (select 1
  from dbo.log s
  where s.Number=r.Number)
  </code></pre>
  
  <p>The above works in checking if the record doesn't exist and it does the insert but the problem with this is that I can't update only the record that was inserted, because it ends up updating everything in the log table.</p>
  
  <p>So how do I get my if statement to work? Am I doing something wrong?</p>