Results 1 to 9 of 9
  1. #1
    ABW Ambassador Greg Rice's Avatar
    Join Date
    January 18th, 2005
    Location
    Ohio
    Posts
    4,889
    I don't understand why this doesn't work. I'm trying to update a mySQL database table with an updated datafeed text file. I only want to update some columns in the table. Here's the syntax I used:

    LOAD DATA LOCAL INFILE 'textfile.txt' REPLACE INTO TABLE `TableName`
    FIELDS TERMINATED BY '|'(
    `Price`
    )

    The test table I'm using only has 9 rows in it, as does the update text file. When I try the REPLACE, it says 17 rows are affected even though there are still only 9 rows. It doesn't update the Price column but instead took the primary key from the last row and inserted it into the Price column of a new row.

    Any idea what is happening? What's wrong with the snytax?
    Greg Rice Affiliate Program Management
    www.gocmc.com info(AT)gocmc.com | 330-259-1223

    Join us! - MiNeeds.com | DiscountCandleShop/CheeseSupply | Feng Shui Plaza

  2. #2
    ABW Ambassador
    Join Date
    January 18th, 2005
    Location
    ÄúsTrálíĺ
    Posts
    1,372
    If I recall correctly, using replace into doesn't keep a rows primary key.

    It's the same as a delete and insert (which would create a new primary id)

    (but this doesn't solve your problem... just an fyi)

  3. #3
    ABW Ambassador Greg Rice's Avatar
    Join Date
    January 18th, 2005
    Location
    Ohio
    Posts
    4,889
    Thanks, Pete. That's one thing I'm confused about since one option is to select the columns to be updated. If it replaces the entire row, then why be able to select columns?
    Greg Rice Affiliate Program Management
    www.gocmc.com info(AT)gocmc.com | 330-259-1223

    Join us! - MiNeeds.com | DiscountCandleShop/CheeseSupply | Feng Shui Plaza

  4. #4
    ABW Ambassador Greg Rice's Avatar
    Join Date
    January 18th, 2005
    Location
    Ohio
    Posts
    4,889
    Maybe a better question would be, how do you update your database table without updating all columns? What I'm trying to do is update a merchant's table without updating all the columns (like categories, since some merchants seem to like to change category names often).
    Greg Rice Affiliate Program Management
    www.gocmc.com info(AT)gocmc.com | 330-259-1223

    Join us! - MiNeeds.com | DiscountCandleShop/CheeseSupply | Feng Shui Plaza

  5. #5
    Member
    Join Date
    January 18th, 2005
    Posts
    148
    Here is the link to check your syntax:

    http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html

    Additionally, you might want to look at phpMyAdmin for working with your database, it definitely streamlines processes and makes it very easy to manage your data.

    ATKOgirl

  6. #6
    Newbie
    Join Date
    January 18th, 2005
    Posts
    2
    You are going to have to do this in two steps.

    In the first step, you will do the "LOAD DATA" into a newly created table. I don't know what fields you have in your file, but the ones you will need are the primary key and the one that you want to update. So perhaps you have part_no and price fields.

    In the second step, you will update the primary table with the updates. You would do this as follows:

    update master,updates set master.price=updates.price where master.part_no=updates.part_no;

    That would copy, row-by-row, from the updated data into the original data.

    I just tried this out and it works fine:

    <pre class="ip-ubbcode-code-pre">
    describe master;
    +---------+-----------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +---------+-----------+------+-----+---------+-------+
    | part_no | int(11) | | PRI | 0 | |
    | price | float | YES | | NULL | |
    | descr | char(255) | YES | | NULL | |
    +---------+-----------+------+-----+---------+-------+

    describe updates;
    +---------+---------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +---------+---------+------+-----+---------+-------+
    | part_no | int(11) | | PRI | 0 | |
    | price | float | YES | | NULL | |
    +---------+---------+------+-----+---------+-------+

    select * from master;
    +---------+-------+---------+
    | part_no | price | descr |
    +---------+-------+---------+
    | 1 | 100 | thing 1 |
    | 2 | 200 | thing 2 |
    | 3 | 300 | thing 3 |
    +---------+-------+---------+


    select * from updates;
    +---------+-------+
    | part_no | price |
    +---------+-------+
    | 1 | 110 |
    | 2 | 220 |
    +---------+-------+

    update master,updates set master.price=updates.price where master.part_no = updates.part_no;


    select * from master;
    +---------+-------+---------+
    | part_no | price | descr |
    +---------+-------+---------+
    | 1 | 110 | thing 1 |
    | 2 | 220 | thing 2 |
    | 3 | 300 | thing 3 |
    +---------+-------+---------+

    </pre>

  7. #7
    ABW Ambassador Greg Rice's Avatar
    Join Date
    January 18th, 2005
    Location
    Ohio
    Posts
    4,889
    Thanks, Jeff. I couldn't figure out how to load only the primary key and updated columns since the datafeed has all the fields. So, I did something similar to what you have. I created a duplicate table and loaded the entire datafeed into it. Then I ran the UPDATE from the new table to the other table, updating only the prices. I then did a LOAD DATA IGNORE to add any new items. This worked well and updated and added.

    Is your way quicker than loading the entire feed? Some of the feeds and large and loading the entire feed only took 1 to 2 minutes each. How do you load the text file and only get certain fields?
    Greg Rice Affiliate Program Management
    www.gocmc.com info(AT)gocmc.com | 330-259-1223

    Join us! - MiNeeds.com | DiscountCandleShop/CheeseSupply | Feng Shui Plaza

  8. #8
    Newbie
    Join Date
    January 18th, 2005
    Posts
    2
    It is possible to instruct LOAD DATA to pull in a subset of the columns. Unless your feeds are huge (hundreds of thousands of records) I would not expect any of these methods to take much time.

    If things are running slowly, make sure that you have the right fields indexed, try the EXPLAIN command to make sure that the indices will be used, and most of all, remember that MySQL will use at most one index per table per command. The new O'Reilly book by Jeremy Zawodny is a great resource for MySQL tuning.

  9. #9
    ABW Ambassador Greg Rice's Avatar
    Join Date
    January 18th, 2005
    Location
    Ohio
    Posts
    4,889
    Thanks. The ones that loaded slowly were large, over 200,000 products. I'll check into that LOAD DATA query.
    Greg Rice Affiliate Program Management
    www.gocmc.com info(AT)gocmc.com | 330-259-1223

    Join us! - MiNeeds.com | DiscountCandleShop/CheeseSupply | Feng Shui Plaza

  10. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Replace Command
    By wow100 in forum WebMerge (Fourthworld.com)
    Replies: 1
    Last Post: November 11th, 2009, 05:48 PM
  2. How can I replace commas. Replace wont' do it
    By sotonman in forum WebMerge (Fourthworld.com)
    Replies: 8
    Last Post: April 25th, 2007, 04:37 AM
  3. Replace Tag
    By misterdave in forum WebMerge (Fourthworld.com)
    Replies: 1
    Last Post: February 26th, 2007, 11:58 AM
  4. Do we have to replace all our CJ links?
    By Morethanable in forum Midnight Cafe'
    Replies: 1
    Last Post: June 28th, 2006, 04:47 PM
  5. Need to replace Old Glory
    By SSanf in forum Midnight Cafe'
    Replies: 6
    Last Post: March 18th, 2003, 04:54 PM

Posting Permissions

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