SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    I meant that to happen silver trophybronze trophy Raffles's Avatar
    Join Date
    Sep 2005
    Location
    Tanzania
    Posts
    4,662
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Merge tables into one and add new column at the same time

    I have lots of tables with identical structures. I want to put them into one single table. Primary key will be updated, so I'm SELECTing everything else. However, I want to have a new column in the new table. The value of this column should contain the name of the old table.

    So, all the rows from "oldtable1" should be moved into new_table, and the "old" column should have the value "oldtable1", for these rows.

    I tried this but it doesn't work. I'm doing it in PHP, where $name is the name of the old table, and this is part of a loop where $name changes with each iteration.

    Code mysql:
    CREATE TABLE IF NOT EXISTS $new_table (
      id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
      old VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL,
      email VARCHAR(255) NOT NULL,
      phone VARCHAR(255) NOT NULL,
      timestamp INTEGER NOT NULL,
      quantity TINYINT(1) NOT NULL DEFAULT 0
    ) ENGINE = MyISAM CHARACTER SET utf8 COLLATE utf8_unicode_ci;
     
     
    INSERT INTO $new_table (name, email, phone, timestamp, quantity)
    SELECT name, email, phone, timestamp, quantity
    FROM $name
    SET old=$name;
    It's the SET old=$name bit at the end that's breaking it. I've thought about simply altering the old table with the new column, putting in the value into every row of that column and then doing the move, but I'm wondering if there's a simpler way.

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    You can do it like this:

    Code MySQL:
    INSERT INTO $new_table (
       name
     , old
     , email
     , phone
     , TIMESTAMP
     , quantity
    )
    SELECT
       name
     , "$name"
     , email
     , phone
     , TIMESTAMP
     , quantity
    FROM
       $name

    Basically you just define a column with a constant value of $name
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    I meant that to happen silver trophybronze trophy Raffles's Avatar
    Join Date
    Sep 2005
    Location
    Tanzania
    Posts
    4,662
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Doh. So simple. Thanks!


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
  •