Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2011
    Posts
    3

    Unanswered: how to replace contents of one column in a mysql db from a text file

    hi

    i am using phpMyAdmin - 2.11.11.3 and MySQL client version: 4.1.22 using the web interface of phpMyAdmin

    i have a mysql db table with a column called added_on where the data in it was wiped out. i have the data for that column in a text file but i dont know how to place it in the mysql db online

    i wish it were as easy as copying and pasting into excel for example, but this is a little different

    i have been trying to use the import command at the web page, but the data is not getting into the column

    i realize this is a very basic question, but i have no idea how to get the data back into that column

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please do a SHOW CREATE TABLE for your table

    also, what fields are available in the text file for matching up with the table?

    you should import the text file into its own table, and do a SHOW CREATE TABLE on that one too

    meanwhile, i've asked the moderators to move this thread to the mysql forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2011
    Posts
    3
    This is the code when i created the contents of the affected table to a copy so as i figured out how to restore the added_on column i wouldnt mess up the original.



    Code:
    CREATE TABLE `accessmy_clone`.`files_to_notes_bu_rev` (
    `notes_id` bigint( 20 ) NOT NULL default '0',
    `files_id` int( 11 ) default NULL ,
    `added_by` bigint( 20 ) default NULL ,
    `added_on` int( 11 ) default NULL ,
    `notes` mediumtext,
    `notes_category` int( 1 ) NOT NULL default '0',
    PRIMARY KEY ( `notes_id` )
    ) ENGINE = MYISAM DEFAULT CHARSET = latin1;
    
    INSERT INTO `accessmy_clone`.`files_to_notes_bu_rev`
    SELECT *
    FROM `accessmy_clone`.`files_to_notes` ;
    as far as the file that has the data, it is one column saved in a text file, with 119,000 rows, the exact same number as the table noted above. it is just a raw txt file. if it needs to be another sort of file i can convert it to suit

    thanks for the reply

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    um... your text file has only one column?

    i'm afraid you're going to have to write some application code to do the update

    you won't be able to process the updates with only sql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Moved thread to MySQL forum.

  6. #6
    Join Date
    Apr 2011
    Posts
    3
    ok, so let's say i export all the data with all the columns, then replace the column with the missing data with the data it needs to have, and save it as a csv file, is there a way to import that into a new table or an existing one?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by inosent View Post
    ok, so let's say i ...
    yes, that will work, you would import the csv using the LOAD DATA INFILE command
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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