TSQL to insert an ascending value

Question

I am running some SQL that identifies records which need to be marked for deletion and to insert a value into those records. This value must be changed to render the record useless and each record must be changed to a unique value because of a database constraint.

UPDATE Users
SET Username = 'Deleted' + (ISNULL(
         Cast(SELECT RIGHT(MAX(Username),1)
              FROM Users WHERE Username LIKE 'Deleted%') AS INT)
                  ,0) + 1
FROM Users a LEFT OUTER JOIN #ADUSERS b ON
a.Username = 'AVSOMPOL\' + b.sAMAccountName
WHERE (b.sAMAccountName is NULL
AND a.Username LIKE 'AVSOMPOL%') OR b.userAccountControl = 514

This is the important bit:

    SET Username = 'Deleted' + (ISNULL(
         Cast(SELECT RIGHT(MAX(Username),1)
              FROM Users WHERE Username LIKE 'Deleted%') AS INT)
                  ,0) + 1

What I've tried to do is have deleted records have their Username field set to 'Deletedxxx'. The ISNULL is needed because there may be no records matching the SELECT RIGHT(MAX(Username),1) FROM Users WHERE Username LIKE 'Deleted%' statement and this will return NULL.

I get a syntax error when trying to parse this (Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'SELECT'. Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ')'.

I'm sure there must be a better way to go about this, any ideas?

Answer 1

If your Users table already has an integer PK column, you can simply use this column to generate 'Deleted'+PK usernames.

Btw, would the SELECT RIGHT(MAX(Username),1) not fail after 10 users? Better to use SUBSTRING().

Answer 2

I suspect this would work better as a multi-step SQL statement, but I'm unsure if that's reasonable.

The error you're seeing is because you're trying to concatenate an int to a string, you're also adding 1. Your order of operations is all screwy in that set statement. This does what you're asking, but it will fail the minute you get more than 9 deleted entries.

SELECT 'DELETED' + CAST(
ISNULL(
CAST(
SELECT RIGHT(MAX(Username),1) 
FROM #Users WHERE username LIKE 'DELETED%') 
AS INT)
, 0) + 1 ) 
AS VARCHAR(3))

edit: sorry for the horrible formatting. Couldn't figure out how to make it readable.

Answer 3

Is it strictly necessary to use incremental 'xxx' values? Couldn't you just use random values?

SET Username = Username + '_deleted_' + CAST(NEWID() AS char(36))

Additionally, it might be a bad idea to overwrite the login completely. Given that you disable the record, not delete it entirely, I assume that you need it for audit purposes or smth. like that. In this case, records with IDs like 'Deleted1234' might be too anonymous.