title: UPDATE Stored Procedure not Updating
body: <p>I've got a SQL Server stored procedure that references a table in my database where users can manually update values for a <code>rent</code> field (<code>'Rent1'</code>). The procedure compares this rent value to a rent field in a different table (<code>'Rent2'</code>). If <code>Rent1</code> is different from <code>Rent2</code> the value for <code>Rent2</code> Is updated to the value of <code>Rent1</code>... or at least that's what is supposed to happen.</p>
  
  <p>When I execute this stored procedure, it runs fine and I receive these output messages:</p>
  
  <pre><code>(1 row(s) affected)
  
  
  (1 row(s) affected)
  </code></pre>
  
  <p>Which is the result i'd expect, because as a means of testing, I have changed two values to be different between <code>Rent1</code> and <code>Rent2</code>. But then when I query my updated table, the values remain unchanged.</p>
  
  <p>Here's my stored procedure:</p>
  
  <pre><code>SET QUOTED_IDENTIFIER ON 
  GO
  SET ANSI_NULLS ON 
  GO
  
  ALTER PROCEDURE update_rent
  AS
  DECLARE @flag INT
  SET @flag = (select COUNT(*) from unit_rent left outer join unittype on unittype = scode where rent &lt;&gt; srent)
  
  WHILE (@flag &gt; 0)
  
  BEGIN
  
  IF (select min(rent) from unit_rent 
      left outer join unittype on unittype = scode 
      left outer join property on property.scode = unit_rent.pscode
      where rent &lt;&gt; srent) &lt;&gt;
     (select min(srent) from unit_rent 
      left outer join unittype on unittype = scode 
      left outer join property on property.scode = unit_rent.pscode
      where rent &lt;&gt; srent
      and rent in (select min(rent) from unit_rent
      left outer join unittype on unittype = scode 
      left outer join property on property.scode = unit_rent.pscode
      where rent &lt;&gt; srent))
  
  BEGIN
  
  UPDATE unittype
  SET srent = (select min(rent) from unit_rent 
      left outer join unittype on unittype = scode 
      left outer join property on property.scode = unit_rent.pscode
      where rent &lt;&gt; srent)
  WHERE unittype.hmy = (select min(hmy) from unittype left outer join unit_rent on unittype = scode where rent &lt;&gt; srent
          and rent = (select min(rent) from unit_rent left outer join unittype on unittype = scode where rent &lt;&gt; srent))
  
  SET @flag = @flag-1;
  
  END 
  
  END
  
  GO
  SET QUOTED_IDENTIFIER OFF 
  GO
  SET ANSI_NULLS ON 
  GO
  </code></pre>
  
  <p>Can anyone see where I might be going wrong or tell me why my output messages are lying to me? Or maybe a different approach I could take? I'd appreciate any form of help, Thanks!</p>
  
  <p><strong>UPDATE:</strong> Just tried a different approach, same results, just 3 more <code>(1 row(s) addected)</code> messages:</p>
  
  <pre><code>ALTER PROCEDURE update_rent
  AS
  DECLARE @tmprent TABLE (hmy INT, rent decimal(11,2));
  DECLARE @flag INT
  SET @flag = (select COUNT(*) from unit_rent left outer join unittype on unittype = scode where rent &lt;&gt; srent)
  
  INSERT INTO @tmprent (hmy, rent) values (1, 0.00);
  
  WHILE (@flag &gt; 0)
  
  BEGIN
  
  IF (select min(rent) from unit_rent 
      left outer join unittype on unittype = scode 
      left outer join property on property.scode = unit_rent.pscode
      where rent &lt;&gt; srent) &lt;&gt;
     (select min(srent) from unit_rent 
      left outer join unittype on unittype = scode 
      left outer join property on property.scode = unit_rent.pscode
      where rent &lt;&gt; srent
      and rent in (select min(rent) from unit_rent
      left outer join unittype on unittype = scode 
      left outer join property on property.scode = unit_rent.pscode
      where rent &lt;&gt; srent))
  
  BEGIN
  
  UPDATE @tmprent
  SET rent = (select min(rent) from unit_rent 
      left outer join unittype on unittype = scode 
      left outer join property on property.scode = unit_rent.pscode
      where rent &lt;&gt; srent)
  WHERE hmy = 1
  
  UPDATE unittype
  SET srent = (select rent from @tmprent where hmy = 1)
  WHERE unittype.hmy = (select min(hmy) from unittype left outer join unit_rent on unittype = scode where rent &lt;&gt; srent
          and rent = (select min(rent) from unit_rent left outer join unittype on unittype = scode where rent &lt;&gt; srent))
  
  SET @flag = @flag-1;
  
  END 
  
  END
  </code></pre>