title: if exists insert else update
body: <p>I'm on an sql2000 (2008 coming soon, yay!) so I can't use the merge statement yet.  </p>
  
  <p>I have the insert statment but now need to compose the update statement.  I'd like to wrap this in an if exists insert else update statement.  The query is longer and more complex but if I can get some help with this basic one below, I think I can get the whole thing.</p>
  
  <pre><code>insert into systemdetail 
      (systemname, projectname, systemtype)
      select  distinct T.systemname, T.projectname, S.model
          from    sysList T, requestSystems S 
          where   T.systemname = S.systemname and 
              S.systemname not in 
              (
              select d.systemname, d.ProjectName from systemdetail d,syslist t2 where t2.systemname = d.SystemName and t2.projectname=d.ProjectName
              )
  </code></pre>
  
  <p>Written out in English:
  If the projectname and systemname exists in the systemdetail table, update it.  Otherwise, insert the projectname and systemname (and other fields) as a new record.</p>
  
  <p>Below is my attempt at writing the if exists but I'm stuck (see ??? within the query).</p>
  
  <pre><code>if (exists (select sd.projectname, sd.systemname from systemdetail sd, sysList t where t.projectname = sd.projectname and t.systemname = sd.systemname)
      update systemDetail 
      set projectname = t.projectname, 
      systemname = t.systemname
      where ??? &lt;-- this is where I'm stuck
  else
      insert into systemdetail 
      (systemname, projectname, systemtype)
      select  distinct T.systemname, T.projectname, S.model
          from    sysList T, requestSystems S 
          where   T.systemname = S.systemname and 
              S.systemname not in 
              (
              select d.systemname, d.ProjectName from systemdetail d,syslist t2 where t2.systemname = d.SystemName and t2.projectname=d.ProjectName
              )
  </code></pre>