title: SQL Server update multiple columns with case in single row
body: <p>I need help in one query. I use SQL Server 2008.</p>
  
  <p>I have three tables (they are more simple, just for example):</p>
  
  <pre><code>create table PriceComponents
  ( id int,
    summ money,
    svkey1 int,
    comission1 float,
    svkey2 int,
    comission2 float,
    to_key int
    );
  
  create table Comissions
  (
  id int,
  commission float,
  svkey int,
  to_key int
  );
  
  create table Goods
  (
  to_key int
  );
  </code></pre>
  
  <p>and simple data in that tables:</p>
  
  <pre><code>insert into Goods values (1), (2), (3);
  
  insert into Comissions values (1, 15, 1, 2), (2, 10, 2, 2), (3, 5, 1, 3);
  
  insert into PriceComponents values (1, 100, 1, 0, 2, 0, 2), (2, 200, 1, 0, 2, 0, 3);
  </code></pre>
  
  <p>And I want to update <code>PriceComponents</code> (columns: <code>comission1</code> and <code>comission2</code>) with that query:</p>
  
  <pre><code>update dbo.PriceComponents
  set comission1 = case when Comissions.svkey=svkey1 then dbo.Comissions.commission else comission1 end,
      comission2 = case when Comissions.svkey=svkey2 then dbo.Comissions.commission else comission2 end
  from dbo.PriceComponents 
  left outer join dbo.Goods on dbo.PriceComponents.to_key = dbo.Goods.to_key 
  left outer join dbo.Comissions on dbo.Goods.to_key = dbo.Comissions.to_key;
  </code></pre>
  
  <p>So before that query <code>PriceComponents</code> has looked like:</p>
  
  <pre><code>id  summ    svkey1  comission1  svkey2  comission2  to_key
  1   100,00  1       0           2       0           2
  2   200,00  1       0           2       0           3
  </code></pre>
  
  <p>and after:</p>
  
  <pre><code>id  summ    svkey1  comission1  svkey2  comission2  to_key
  1   100,00  1       15          2       0           2
  2   200,00  1       5           2       0           3
  </code></pre>
  
  <p>but I expect this result:</p>
  
  <pre><code>id  summ    svkey1  comission1  svkey2  comission2  to_key
  1   100,00  1       15          2       10          2
  2   200,00  1       5           2       0           3
  </code></pre>
  
  <p>I don't really understand where I have an error.</p>