CODEWITHSUNDEEP
sqlsql-server
Joel
Joel

Reputation: 6173

iterative loop to select row

I need to be able to SELECT a specific row because SET only holds one row.

On line 5, I have to declare @json so that OPENJSON can work.

And that's why i need the RowNo at this point in the query. However, at this point, RowNo is not defined.

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 row on line 5 as desired?

DECLARE @loopCounter INT = 0;
WHILE @loopCounter < (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

Upvotes: 0

Views: 45

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

You can use CROSS APPLY instead of a loop. Try:

WITH CTE AS (
    SELECT 
          LocalTime
        , Data
    FROM dbo.ProcessEventMessages
    CROSS APPLY OPENJSON(Data) 
    WITH (  
        LocalTime datetime2(7) '$.time'
     )  AS ChangeTime
    )
UPDATE CTE 
    SET Data = JSON_MODIFY(Data,'$.time', FORMAT(DATEADD(hour,-2,LocalTime),'yyyy-MM-ddTHH:mm:ss.fff'));

Upvotes: 1

Related Questions