title: T-sql update value based on sub select result
body: <p>MS SQL 2008 in use and stored procedure is needed.</p>
  
  <p>So in my table i have certain rows where IsDouble value will be 2. 
  Now i need to update IsDouble from 2 to 1 for each line from the first select if a count of string (concatenated from 4 columns) is greater than 1 and if the count of the string is 1 to 0</p>
  
  <p>So far i have this but it's most likely incorrect:</p>
  
  <pre><code>select * from TestInvoiceData where Isdouble='2';
  update TestInvoiceData
  set testinvoicedata.Isdouble=
  case 
      when  
      (
          select COUNT (InvoiceDate+InvoiceNumber+VendorCode+Invoicetype) 
          from      TestInvoiceData
       ) &gt;1 then 1
       else 0
  end;
  </code></pre>
  
  <p>EDIT</p>
  
  <p>Here's the sample data (invoicetype,invoicenumber,invoicedate,vendorcode):</p>
  
  <pre><code>INVO    322760-262  2012-05-10  0000081964  2
  INVO    322760-262  2012-05-10  0000081964  0
  INVO    322756-262  2012-05-10  0000081964  2
  INVO    7011200072  2012-05-10  0000046172  0
  INVO    7011200071  2012-05-10  0000046172  0
  INVO    7011200070  2012-05-10  0000046172  0
  INVO    7011200069  2012-05-10  0000046172  0
  INVO    7011200068  2012-05-10  0000046172  0
  INVO    12106563    2012-04-24  0000010171  0
  INVO    06649       2012-04-24  0000067987  0
  </code></pre>
  
  <p>and based on this example the first row should be after update 1 and the third one 0</p>