Need help with UPDATE query

[Ryan Mortier]

I recently changed my database structure and I'm attempting to clean up duplicate values.

Right now, my results are in this format:



I want to merge the extension, mobile and other into a single row but I'm having problems with the update query.

Here is my current query:

UPDATE `numbers` SET
    `extension` = (SELECT `extension` FROM `numbers` WHERE `extension` IS NOT NULL)
WHERE `extension` IS NULL AND `contacts_id` = 5
 
UPDATE `numbers` SET
`mobile` = (SELECT `mobile` FROM `numbers` WHERE `mobile` IS NOT NULL)
WHERE `mobile` IS NULL AND `contacts_id` = 5
 
UPDATE `numbers` SET
`other` = (SELECT `other` FROM `numbers` WHERE `other` IS NOT NULL)
WHERE `other` IS NULL AND `contacts_id` = 5

Then I would proceed to delete duplicates after all 3 rows are identical.

Is there a better way to do this?

[guelphdad]

You're only going to have one number for each phone type right? Does it matter which id is chosen? if not try this:

 
CREATE TABLE   
newnumbers 
SELECT   
min(id),   
contacts_id,   
max(extension),   
max(mobile),   
max(other) 
FROM   numbers 
GROUP BY   contacts_id

Then drop your old table and rename the new table:

 DROP TABLE numbers; ALTER TABLE newnumbers RENAME numbers;