Trim characters from a field but only if it doesn't result in a duplicate? I have a database of servers and some servers were entered with the fully qualified domain name.  I want to rename all of these.  However, in some instances the server has been entered both ways.  For instance, servername.domain.com and servername.  When I run my query below, these cause an error because of course that column can only contain unique records.

UPDATE ServerData
SET    ServerName = replace(ServerName, '.domain.com', '')
WHERE  ServerName LIKE '%.domain.com';

Error "Cannot insert duplicate key row in object 'dbo.ServerData' with unique index 'UK_ServerData'."


How can I modify the query to simply ignore records that would generate an error so I can deal with them individually later?
This will check to make sure the value you are writing doesn't already exist in that field:

UPDATE s
SET    ServerName = replace(ServerName, '.domain.com', '')
FROM serverdata S
WHERE  ServerName LIKE '%.domain.com'
AND NOT EXISTS (SELECT 1 
                FROM serverdata s2
                WHERE s2.servername = &lt;&gt; replace(s1.ServerName, '.domain.com', ''))