#1
  1. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,365
    Rep Power
    24

    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 that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,620
    Rep Power
    4287
    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
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,365
    Rep Power
    24
    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 that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,620
    Rep Power
    4287
    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
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,365
    Rep Power
    24
    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 that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.

IMN logo majestic logo threadwatch logo seochat tools logo