CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Sic
Sic

Reputation: 297

UPDATE or DELETE table row depending on existing value

I have table A with Name nvarchar(200) UNIQUE column. I have written a function working simular to NormalizeSpace(trim + collapse multiple spaces to single one), the question is how to write a script that will UPDATE or DELETE Name column dependig on is normalized Name already exists in table or not: if not exists UPDATE, else DELETE.

Example:

  'a bc' 
  ' a  bc'
  ' d  e'

Will result:

  'a bc'
  'd e'

Thank you.

Upvotes: 0

Views: 79

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

You can do this in two steps (or 1 if you use merge):

-- Remove all but one copy of each value
with todelete as (
      select t.*,
             row_number() over (partition by dbo.NormalizeSpace(col) order by (select null)) as seqnum
      from t
     ) 
delete from todelete
    where seqnum > 1;

-- Now update the values to be normalized:
update t
    set col = dbo.NormalizeSpace(col)
    where col <> dbo.NormalizeSpace(col);

Upvotes: 2

Related Questions