CODEWITHSUNDEEP
sqlsql-servert-sql
user1070241
user1070241

Reputation:

TSQL Looping through rows which do not contain sequential ids

I need to loop through each row in a table to perform some action. The primary key of the table is a GUID identifier so I can't really use that in my loop. This is what I have so far:

DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT(EmploymentIdentifier) FROM tblEmployment)

DECLARE @I INT
SET @I = 1

WHILE (@I <= @RowCount)
BEGIN
    --Just some variables I'll be using later
    DECLARE @PaymentTypeOld int, @PaymentTypeNew uniqueidentifier

    -- How do I select each unique row here? I'm stuck at:
    SELECT TOP 1 FROM tblEmployment WHERE EmploymentIdentifier == ???

    //Logic goes here..

    SET @I = @I + 1

END

I tried playing around with ROW_NUMBER() but kept getting syntax error with regards to using OVER incorrectly.

It doesn't matter in what order I process the rows, as long as all rows in the table are processed.

Upvotes: 1

Views: 1190

Answers (1)

xQbert
xQbert

Reputation: 35333

This will only set myField = current value *2 when col1 has a value of 1 When col1 has a value of 2 then myfield would be multiplied by 3. If col1 is anything else myField is left unchanged.

Update myTable set MyField = Case when Col1 = 1 then MyField*2  
                                  When Col1 = 2 then MyField*3
                                                else myfield end

You could also do multiple conditions... now both Col1 must be 1 and col2 must be N before we multiply results by 2.

Update myTable set MyField = Case when Col1 = 1 and Col2 ='N' then MyField*2 
                                                              else MyField end

It could be a simple where clause too which reduces the overhead of records to process seems like the most efficient if we only need to update certain records to a specific value, but if all records must be updated and the update varies by record, then case statement is the way to go..

This simply multiples myField by 2 when col1 is 1.

Update mytable set myField = myField*2 where col1=1

Upvotes: 1

Related Questions