title: iterative loop to select row
body: <p>I need to be able to SELECT a specific row because SET only holds one row.</p>
  
  <p>On line 5, I have to declare @json so that <code>OPENJSON</code> can work. </p>
  
  <p>And that's why i need the RowNo at this point in the query. However, at this point, <code>RowNo</code> is not defined. </p>
  
  <p>How would i go about fixing this problem? It seems that it doesn't matter how i spin it, something is always gonna be undefined. How would i point to a specific <code>row</code> on line 5 as desired?</p>
  
  <pre><code>DECLARE @loopCounter INT = 0;
  WHILE @loopCounter &lt; (SELECT count(Id) FROM ProcessEventMessages)
  BEGIN
      PRINT 'Changing...';
  DECLARE @json NVARCHAR(MAX) = (SELECT Data FROM ProcessEventMessages WHERE RowNo = @loopCounter) -- here
  SELECT * FROM OPENJSON(@json) 
  WITH (  
      message varchar(200) '$.message',
      machineId varchar(200) '$.machineId',
      machineName int '$.machineName',
      ipAddress varchar(200) '$.ipAddress',
      LocalTime datetime2(7) '$.time'
   )  AS ChangeTime;
  
     ;WITH CTE AS(
      SELECT *,ROW_NUMBER() OVER(Order by Id ASC) AS RowNo
      FROM ProcessEventMessages 
      )
      UPDATE CTE 
  
      SET Data = JSON_MODIFY(@json,'$.time', FORMAT(DATEADD(hour,-2,JSON_VALUE(@json,'$.time')),'yyyy-MM-ddTHH:mm:ss.fff')) 
      WHERE RowNo = @loopCounter
  
     SET @loopCounter = @loopCounter + 1;
  END;
  
  PRINT 'Done';
  GO
  </code></pre>