Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2011
    Posts
    5

    Unanswered: how to update the record with batch in phpmyadmin

    the table named: `products`. it have five hundred rows record. the table have field :`products_model` `products_price`. the `products_model` is unique。

    the original value that stored in `products` table are all `0.0000`. now i want to update it with batch in phpmyadmin according to the 'products_model`.

    i prepared the data in `excel` with two columns, 'products_model` the new value `products_price`(according to some reason, some may be empty). how do i do? thank you.

    the origina data :

    products_model products_price
    LB2100 0.00
    LB2117 0.00
    LB2381 0.00
    LB2307 0.00
    LB2380 0.00

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    start with your excel file

    save it as a tab-separated csv file

    then look up LOAD DATA INFILE in the mysql manual

    this will help you load the excel data into a mysql table

    that should get you started

    once this is ready, you will use a joined update to update your main table

    the syntax for this is explained in the manual under UPDATE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2011
    Posts
    5
    Quote Originally Posted by r937 View Post
    start with your excel file

    save it as a tab-separated csv file

    then look up LOAD DATA INFILE in the mysql manual

    this will help you load the excel data into a mysql table

    that should get you started

    once this is ready, you will use a joined update to update your main table

    the syntax for this is explained in the manual under UPDATE
    1, what's the tab-separated csv file. does it like this?

    products_model products_price
    LB2100
    LB2117 12.49
    LB2381 35.99
    LB2307 12.99
    LB2380 35.99
    LB2468 10.99
    LB2139
    2,supposed the file named product.csv, when using LOAD DATA INFILE 'product.csv'
    where should i put the csv file into? i put it on my local computer, how do i load?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1. sort of, yes, assuming you have tabs between the fields
    2. into a table that you declare, that has two columns, to receive the fields from the csv
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2011
    Posts
    5
    Quote Originally Posted by r937 View Post
    1. sort of, yes, assuming you have tabs between the fields
    2. into a table that you declare, that has two columns, to receive the fields from the csv
    1,the original data is stored in the database as the image shows. the table is products.
    http://www.dbforums.com/attachment.p...1&d=1320374610

    2, the data in excel is as the following shows.
    http://www.dbforums.com/attachment.p...1&d=1320373322

    3,but when i open it by notepad++. it shows like this:

    http://www.dbforums.com/attachment.p...1&d=1320373367

    one: the data's format which i prepared is right? if not? how to correct it?

    two: how to use joined update?

    three: i using this command in phpmyadmin, but the data doesn't be updated.



    Code:
    LOAD DATA  LOCAL INFILE 'D:\\test.csv' REPLACE INTO TABLE products
    FIELDS TERMINATED BY '  '
    LINES TERMINATED BY '\r\n'
    IGNORE 1 LINES
    (products_model, @var1)
    SET products_price = IF(@var1 = '', 0, @var1); -- Replace empty string with 0
    the command can run, but the data doesn't be updated. thank you.
    Attached Thumbnails Attached Thumbnails 2011-11-04_094357.jpg   2011-11-04_094623.jpg   2011-11-04_094902.jpg   2011-11-04_104123.jpg  
    Last edited by runeveryday; 11-03-11 at 23:43.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by runeveryday View Post
    one: the data's format which i prepared is right? if not? how to correct it?
    did you save it from excel as a tab-delimited csv?

    Quote Originally Posted by runeveryday View Post
    two: how to use joined update?
    look it up in the manual

    Quote Originally Posted by runeveryday View Post
    three: i using this command in phpmyadmin, but the data doesn't be updated.
    you're not supposed to load the csv into your main table, you're supposed to load the csv into a separate table, and then use the separate table to update the products table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    When defining the LOAD INTO command use '\t' for a tab:

    Code:
    LOAD DATA  LOCAL INFILE 'D:\\test.csv' REPLACE INTO TABLE products
    FIELDS TERMINATED BY '\t'
    LINES TERMINATED BY '\r\n'
    IGNORE 1 LINES
    (products_model, @var1)
    SET products_price = IF(@var1 = '', 0, @var1);
    Also using REPLACE will replace the entries that already exist based on the primary key. While this works, I would do what Rudy is suggesting which is load the data into a staging table and then use an UPDATE statement in order to migrate the data to the live table. I am speaking from experience where the load process or file format changes between runs and then you can end up with garbage and nothing to fall back on.
    Last edited by it-iss.com; 11-04-11 at 05:20.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  8. #8
    Join Date
    Nov 2011
    Posts
    5
    Quote Originally Posted by r937 View Post
    did you save it from excel as a tab-delimited csv?

    look it up in the manual

    you're not supposed to load the csv into your main table, you're supposed to load the csv into a separate table, and then use the separate table to update the products table
    1,i don't know how to save the csv file from excel as a tab-delimited csv?

    2, you mean i must create a new table then stored the csv data in it. then using it to update the products table.

    i am very sorry, it hard for me to write the sql command.

  9. #9
    Join Date
    Nov 2011
    Posts
    5
    Quote Originally Posted by it-iss.com View Post
    When defining the LOAD INTO command use '\t' for a tab:

    Code:
    LOAD DATA  LOCAL INFILE 'D:\\test.csv' REPLACE INTO TABLE products
    FIELDS TERMINATED BY '\t'
    LINES TERMINATED BY '\r\n'
    IGNORE 1 LINES
    (products_model, @var1)
    SET products_price = IF(@var1 = '', 0, @var1);
    Also using REPLACE will replace the entries that already exist based on the primary key. While this works, I would do what Rudy is suggesting which is load the data into a staging table and then use an UPDATE statement in order to migrate the data to the live table. I am speaking from experience where the load process or file format changes between runs and then you can end up with garbage and nothing to fall back on.
    when i used REPLACE it didn't replace the entries that already exist, it inserted new lines into the products table, i don't know why?

    in the products table, the products_id is the primary key. but products_model is also unique.but not the primary key.

    3, how to use UPDATE to update the products table with the staging table?
    Last edited by runeveryday; 11-04-11 at 06:46.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by runeveryday View Post
    1,i don't know how to save the csv file from excel as a tab-delimited csv?
    it's a pretty common thing to do

    maybe start a thread in some excel forum?

    maybe press F1 (Help) when you're in excel, and look it up?


    Quote Originally Posted by runeveryday View Post
    2, you mean i must create a new table then stored the csv data in it. then using it to update the products table.
    this is what i have been saying all along, yes



    Quote Originally Posted by runeveryday View Post
    3, how to use UPDATE to update the products table with the staging table?
    i keep telling you, look it up in the mysql manual, under the UPDATE statement
    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
  •