Merge Duplicate Computer Records in Deployment Server

For the longest time I have been looking for this script, but I had no luck. Thank you for writing it, it will be helpful if what I am doing now does not work.
The only thing that I could find to make it so I do not have dupicates was to go to Tools -> Options -> Global tab and put a check next to Synchronize display names with computer names.
The only time that I would think this would not work for some is if the computer name differs from the label it is given in DS.
Again, thank you very much for posting the SQL query. Cheers

Wow thanks for the query.  I know we've been looking for something like this for quite awhile too.  With MAC address set as our primary key the VPN software we use can lead to multiple entries quite frequently.  This should be a huge help.

I ran this, but I get the following error:

Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '='.
Msg 156, Level 15, State 1, Line 41
Incorrect syntax near the keyword 'FROM'.

Any ideas?  We have several duplicate computers.......mostly because we do it by MAC Address, but then laptops that are used wired and wireless create cuplicates. 

May have to start using serial number as a lookup key.

Looks like it is working now.  Didn't notice that there was some code included on some of the lines that had the comments.  Had to do a little editing.  Thanks!

Decided to post a working version where the parts where the code bled into the comments have been fixed already, so that people (like me) who are looking for this don't need to do the work again later... Not using Preformatting, because it's doing nasty things to the alignment and so on.

--Create a table variable to pair duplicate records for merging
DECLARE @ComputerIDs TABLE
(
  NewCompID int,
  OldCompID int
)
--Find all computers with more than one ID. The larger number is the younger computer record, the smaller number is the older computer record.
INSERT INTO @ComputerIDs (NewCompID, OldCompID)
  SELECT MAX(computer_id) AS NewCompID, MIN(computer_id) AS OldCompID
    FROM computer
    GROUP BY computer_name HAVING COUNT(1) = 2
 --Now update the younger computer record with all the data from the old computer record
--Get the Group ID from the old computer record
UPDATE N
  SET group_id = O.group_id
  FROM computer N
    JOIN @ComputerIDs I ON N.computer_id = I.NewCompID
    JOIN computer O ON I.OldCompID = O.computer_id

--Delete location from the new record
DELETE FROM location WHERE computer_id IN (SELECT NewCompID FROM @ComputerIDs)

--Delete service processors from the new record
DELETE FROM service_processor WHERE computer_id IN (SELECT NewCompID FROM @ComputerIDs)

--Move location from the old record to the new record
UPDATE T SET computer_id = I.NewCompID
  FROM location T
    JOIN @ComputerIDs I ON T.computer_id = I.OldCompID

--Move service processors from the old record to the new record
UPDATE T SET computer_id = I.NewCompID
  FROM service_processor T
    JOIN @ComputerIDs I ON T.computer_id = I.OldCompID

--Move config items from the old record to the new record
UPDATE T SET target_computer_id = I.NewCompID
  FROM config_item T
    JOIN @ComputerIDs I ON T.target_computer_id = I.OldCompID

--Move event schedules from the old record to the new record
UPDATE T SET computer_id = I.NewCompID
  FROM event_schedule T
    JOIN @ComputerIDs I ON T.computer_id = I.OldCompID

--Move history from the old record to the new record
UPDATE T SET computer_id = I.NewCompID
  FROM history T
    JOIN @ComputerIDs I ON T.computer_id = I.OldCompID
UPDATE T SET computer_id = I.NewCompID
  FROM history_archive T
    JOIN @ComputerIDs I ON T.computer_id = I.OldCompID

--Move status log from the old record to the new record
UPDATE T SET computer_id = I.NewCompID
  FROM status_log T
    JOIN @ComputerIDs I ON T.computer_id = I.OldCompID
UPDATE T SET computer_id = I.NewCompID
  FROM status_log_archive T
    JOIN @ComputerIDs I ON T.computer_id = I.OldCompID

--Delete old computer records
DELETE FROM computer WHERE computer_id IN (SELECT OldCompID FROM @ComputerIDs)