CODEWITHSUNDEEP
mysqlubuntuload-data-infile
vishu
vishu

Reputation: 77

Mysql Load Data for existing column of a table

Initially I have uploaded Using load Data Infile row is having like 100000 Im Using Ubuntu

Example:data

ToneCode....Artist...MovieName...Language

1....................Mj..........Null........... English

3....................AB..........Null........... English

4....................CD.........Null........... English

5....................EF..........Null........... English

But Now I have To update Column MovieName Starting From ToneCode 1 till 100000 row I’m having data in .csv file to update . Please suggest how to upload the .Csv file for existing table with data

Upvotes: 5

Views: 529

Answers (2)

C3roe
C3roe

Reputation: 96454

I think the fastest way to do this, using purely MySQL and no extra scripting, would be as follows:

  • CREATE a temporary table, two columns ToneCode and MovieName same as in your target table
  • load the data from your new CSV file into that using LOAD DATA INFILE

  • UPDATE your target table using the INNER JOIN-like syntax that http://dev.mysql.com/doc/refman/5.1/en/update.html describes:

    UPDATE items,month SET items.price=month.price WHERE items.id=month.id;

    this would “join” the two tables items and month (by using just the “comma-syntax” for an INNER JOIN) using the id column as the join criterion, and update the items.price column with the value of the month.price column.

Upvotes: 2

vishu
vishu

Reputation: 77

I Have found a solution as u Guys mentioned above

Soln: example

create table A(Id int primary Key, Name Varchar(20),Artist Varchar(20),MovieName Varchar(20));

Add all my 100000 row using

Load data infile '/Path/file.csv' into table tablename(A) fields terminated by ',' enclosed by'"' lines terminated by '\n' (Id,Name,Artist) here movie value is null

create temporary table TA(Id int primary Key,MovieName Varchar(20));

Uploaded data to temporary table TA Load data infile '/Path/file.csv' into table tablename(A) fields terminated by ',' enclosed by'"' lines terminated by '\n'(IDx,MovieName)

Now using join as u said

Update Tablename(TA),TableName(A) set A.MovieName=TA.MovieName Where A.Id=TA.Id

Upvotes: 0

Related Questions