#1
  1. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    677
    Rep Power
    7

    Copy table -> altered table


    The whole copy and paste thing I do get. The issue is what is being copied. This is an expense report table. I built a new table because whoever built the last table, made 17 columns, 1 for each category. Mine took the extra columns n turned them into 2. Category & Cost. Every entry provides only 1 cost per row, and leaves the other 16 NULL. I want to copy whichever holds the value. Any way to do this? Thanks a ton!
    He who knows not and knows not he knows not: he is a fool - shun him. He who knows not and knows he knows not: he is simple - teach him. He who knows and knows not he knows: he is asleep - wake him. He who knows and knows he knows: he is wise - follow him
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    Originally Posted by Triple_Nothing
    Every entry provides only 1 cost per row, and leaves the other 16 NULL. I want to copy whichever holds the value.
    use COALESCE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    677
    Rep Power
    7
    Hmmm... Either I'm not understanding the COALESCE, or it not exactly what I'm looking for. One thing that make this project difficult is the HUGE message of a database. :-/ I provided 2 images. The first shows an original table. The second is how I wish it to look after copying things over. Since the DATE column appears to have some NULL values in there, will this still work? BilledTo now holds an ID number to the definition of what the column header used to be on older table.
    Attached Images
    He who knows not and knows not he knows not: he is a fool - shun him. He who knows not and knows he knows not: he is simple - teach him. He who knows and knows not he knows: he is asleep - wake him. He who knows and knows he knows: he is wise - follow him
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    to copy rows over, you are using INSERT SELECT, right?
    Code:
    INSERT 
      INTO your_new_table
         ( id
         , erid
         , date
         , explanation
         , billedto
         , cost )
    SELECT id
         , erid
         , date2
         , explanation
         , billedto
         , COALESCE(airfare
                  , autorental
                  , taxi
                  , meals
                  , mileage
                  , ent
                  , tollsparking
                  , phone
                  , hotel
                  , other
                  , corpvisa )
    i'm not sure what you're doing with that billedto number, but this is how you use COALESCE -- to pick up the non-null value from whatever column it's in
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    677
    Rep Power
    7
    Ah, now that example I understand... ^_^ Thank you very much.

    As far as my BillTo, kinda tossin a coin which be easiest. Run 1 SELECT, have PHP match BillTo to a category ID number, then run an INSERT, OR just copy over, and see if there is a way just to Find & Replace, and change to ID numbers that way.
    He who knows not and knows not he knows not: he is a fool - shun him. He who knows not and knows he knows not: he is simple - teach him. He who knows and knows not he knows: he is asleep - wake him. He who knows and knows he knows: he is wise - follow him

IMN logo majestic logo threadwatch logo seochat tools logo