SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help with UPDATE query

    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:

    Code SQL:
    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?

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You're only going to have one number for each phone type right? Does it matter which id is chosen? if not try this:
    Code:
     
    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:
    Code:
     DROP TABLE numbers; ALTER TABLE newnumbers RENAME numbers;


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •