SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    I'm not a human
    Join Date
    Aug 2006
    Location
    India
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to join to two tables and update columns with null or default value?

    I have two different tables and i decided to merge them into one table...

    the birth table has

    id, name, country, birthday_date, description, link

    the death table has
    id, name, country, death_date, description, link

    and i want to merge them into a single table with the structure

    id, name, country, bdate, ddate, description, link.

    The link from each table has a unique value so i have to merge the tables using the link. I tried many queries but resulted in wrong results.

    Both the birth and death table can have same names and some names may have either only birth or death date.

    How can i merge them and updating a null date for a column that has no value on either tables?
    Regards,
    Vijay
    Follow me on twitter @vijaycbe
    World Holiday Calander || My Cricket Blog

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    #Create the new table
    
    CREATE TABLE people (id INT, name VARCHAR(255), country CHAR(2), bdate DATE, ddate DATE, description TEXT, link VARCHAR(255));
    
    # Populate it from the birth table
    
    INSERT INTO people (id, country, bdate, description, link) SELECT id, country, bdate, description, link FROM birth;
    
    # Fill in missing fields using the death table
    
    UPDATE people, death SET people.id = death.id WHERE people.id IS NULL AND people.link = death.link;
    
    UPDATE people, death SET people.name = death.name WHERE people.name IS NULL AND people.link = death.link;
    
    # ..etc, repeated for each column

  3. #3
    I'm not a human
    Join Date
    Aug 2006
    Location
    India
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is there any other query to insert data for all columns?
    Regards,
    Vijay
    Follow me on twitter @vijaycbe
    World Holiday Calander || My Cricket Blog

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Why, is copying and pasting three times too much work? This is a one time thing, right?


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
  •