When doing an update on a set of records, how do i get previous updates to show on current row to update in UDF [closed]

Question

SO i have a rowset of 10 UIDs. They are all dupes, lets say DUPEUID. I have a UDF that will take the UID, check it against the table to see if there is a dupe, and update the UID to DUPEUID01.

The problem i'm having is that my update statement updates a rowset. The first row gets updated, but when the 2nd row is evaluated the previous updated row is not found during the SELECT. I suspect i am running into an isolation issue.

I have tried REPEATABE, SERIALIZABLE, to no avail.

is there no way to do this in a rowset operation? DO i have to create a cursor and loop through all the dupes?

Here's a rough example:

there are 3 rows

UID  
-------  
UIDDUPE  
UIDDUPE  
UIDDUPE

all 3 rows same value.

UDF that goes and does this: select * from uid table looking for dupes.

and an update that calls the UDF

update table
set UID = dbo.UIDFIX(UID)

when the update runs , it updates the first row to UIDDUPE01, when it gets to the 2nd row, it does a select,and does NOT find the previously updated row. so it creates ANOTHER UIDDUPE01.

The logic for updating these UIDS are much MORE complicated than the simple way i've shown it here. The accepted answer will stick to my question, and not say well you shouldnt' use..... This is the real world. sometimes we have horrible situations.

The question has to do with UDF's and set based consistency?

Answer 1

(You edited your question, so now I'm editing my answer)

Stop using a UDF for it. Do it all in one hit like this:

WITH Numbered AS 
( SELECT *,
  ROW_NUMBER() OVER (PARTITION BY UID ORDER BY UID) AS DupeNum
  FROM theTable
)
UPDATE Numbered SET UID = UID + '_' + CAST(DupeNum AS VARCHAR(10))
WHERE DupeNum > 1;

By doing it all at once, you will avoid the issues you have, because it works out what they should all become and applies them in a single transaction.

Also - put an underscore or something in. If you have UIDs like D, D, D2, then it's better to end up with D, D_2, D1 than D, D2, D2. Use a character that isn't used in regular UIDs. And ideally, store what it was originally somewhere, because if you get more Ds coming through, you want to be able to tell that you really have a third duplicate, not just a second one.

A further edit to clarify:

A scalar UDF is a procedure. It has BEGIN and END, and runs row by row. When this runs repeatedly in a larger query, it runs within the context of the transaction, and therefore sees the impact of earlier work within the transaction.

If you MUST use the UDF, create a temporary table containing the rows that you want to update to, and then perform an update by joining this temporary table. While you populate the temporary table you're not making any changes to the table, so every UDF call will be unaffected by those changes. Then you can do it without having a UDF in your actual update statement.

Yet another edit;

What does this do?

WITH Numbered AS 
( SELECT *,
  ROW_NUMBER() OVER (PARTITION BY UID ORDER BY UID) AS DupeNum
  FROM theTable
)
UPDATE Numbered SET UID = dbo.UIDFIX(UID)
WHERE DupeNum > 1;

And more...

How about:

--First get the new values
WITH Numbered AS 
( SELECT *,
  ROW_NUMBER() OVER (PARTITION BY UID ORDER BY UID) AS DupeNum
  FROM theTable
)
SELECT *, dbo.UIDFIX(UID) AS NEWUID
INTO #NewVersion
FROM Numbered
WHERE DupeNum > 1;

--Now update them all
UPDATE theTable
SET UID = n.NEWUID
FROM #NewVersion AS n
WHERE n.PK = theTable.PK;

Answer 2

The issue that you are facing is that, even though UDFs do run per-row, the state of the data, as far as that particular UPDATE statement is concerned, has not changed yet. And it won't register any changes until the statement completes successfully.

This is not to be confused with transaction scope. If you have multiple statements within a transaction, then they can see the effects of prior statements within that same transaction, but that is for statements that have completed. Here you are wanting to see data that is changing within the same statement, and having such an ability would probably mean that SQL Server would then be row-based instead of set-based.

---

In the question you asked:

is there no way to do this in a rowset operation? DO i have to create a cursor and loop through all the dupes?

and in a comment on Rob's answer you stated:

i'm using it in an update statement, so it's rowset based.... my question deals with why doesn't one update row see the previously updated row's value.

There is some confusion here about what constitutes row-based vs set-based operations. Merely calling this UDF in an UPDATE does not make it a set-based operation. It is still row-based because that is actually what you are saying that you want it to be: "one update row see the previously updated row's value". You want the UDF to re-run its query for every row of the UPDATE statement. That is row-based.

While you are trying to avoid using a CURSOR, you still have the same row-by-row structure of an explicit CURSOR. So if using the UDF is a requirement, then using an explicit CURSOR is also a requirement, and you should be ok with that since it is what have you been attempting to do anyway.

Now, you can make the operation more efficient by making all changes in a single transaction, as opposed to each one being its own transaction, by starting the transaction before the CURSOR loop and committing at the end. And, if you have no field or combination of fields that uniquely identify a row, then you need to use the WHERE CURRENT OF syntax of the UPDATE statement.

DECLARE dupes CURSOR
FOR SELECT....

BEGIN TRY

OPEN dupes

FETCH NEXT
FROM  dupes

BEGIN TRAN;

WHILE (@@FETCH_STATUS = 0)
BEGIN
  UPDATE table
  SET    UID = dbo.UIDFIX(UID)
  WHERE  CURRENT OF dupes;

  FETCH NEXT
  FROM  dupes
END;

COMMIT TRAN;

END TRY
BEGIN CATCH

  ROLLBACK TRAN;
  ...other error handling stuffs...

END CATCH;

CLOSE dupes;
DEALLOCATE dupes;