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

inosent · #1 (**permalink**) 04-16-11, 15:19

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

r937 · #2 (**permalink**) 04-16-11, 15:53

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

inosent · #3 (**permalink**) 04-16-11, 16:58

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

r937 · #4 (**permalink**) 04-16-11, 18:48

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

mike_bike_kite · #5 (**permalink**) 04-17-11, 05:44

Moved thread to MySQL forum.

inosent · #6 (**permalink**) 04-17-11, 14:19

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?