SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Evangelist runeveryday's Avatar
    Join Date
    Jul 2009
    Posts
    437
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by runeveryday View Post
    how do i do? thank you.
    load the excel data into another table, and then use a joined update, the syntax for which is explained in da manual
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist runeveryday's Avatar
    Join Date
    Jul 2009
    Posts
    437
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i am sorry, i don't know how to do it? could you say it more clearly. thank you.how to set the data in the excel? and how to load it? how to use a joined update?

  4. #4
    SitePoint Evangelist runeveryday's Avatar
    Join Date
    Jul 2009
    Posts
    437
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    anyone helps?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Evangelist runeveryday's Avatar
    Join Date
    Jul 2009
    Posts
    437
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    1,the original data is stored in the database as the image shows. the table is products.
    2011-11-04_104123.jpg

    2, the data in excel is as the following shows.

    2011-11-04_094623.jpg
    3,but when i open it by notepad++. it shows like this:

    2011-11-04_094902.jpg

    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, supposed the csv file named test.csv. 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.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    "giving out my real stuffs"

  8. #8
    SitePoint Evangelist runeveryday's Avatar
    Join Date
    Jul 2009
    Posts
    437
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you mean fist i must be create a table, then load the csv date into it then use the separate table to update the products table ?

    i am sorry, i don't know how to write the command/

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by runeveryday View Post
    you mean fist i must be create a table, then load the csv date into it then use the separate table to update the products table ?
    this is what i have been saying all along, yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Evangelist runeveryday's Avatar
    Join Date
    Jul 2009
    Posts
    437
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i got it. many thanks. then using the command like the following

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


Bookmarks

Posting Permissions

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