SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    I want my 4th arrow! garlinto's Avatar
    Join Date
    Jun 2002
    Location
    Riding the electron wave
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Update a table or copy changes to new table?

    My venders send me an updated pricelist every quarter. It is in Excel format, but I convert it into comma-delimited text files and then import it into a mysql table.

    I have made additions to my existing pricelist table ( added more products ) that do not exist in the vender's pricelist. But, my vender is always adding new products to their pricelist as well.

    So, my question is:
    Should I just use each newly-created pricelist table and use a php script to make my own custom additions? Or should I update my original table using the new one, as well as insert the new products?

    I'm wondering because I want to maintain the data integrity of all of the invoices that are stored in the database. All of the product information for them is pulled off of the pricelist. I'm worried about data corruption that could result from a mishandled update.

    What do you folks think would be the best way to handle this?
    Ducharme's Axiom: "If you view your problem closely
    enough, you will recognize yourself as part of the problem."


  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    suggest you datestamp your existing rows, and "retire" all rows that are included in any new pricelist, which you insert, not update

    in other words, if you have a widget now, it will look like this --
    Code:
    product price effectivefrom effectiveto
     widget  9.37   2005-01-15     null
    the NULL indicates it is a current price

    now suppose your supplier sends you a new file, and it contains an entry for widget

    first, load the new pricelist into a holding table

    then update all products in your table which match (using an update join), setting their effectiveto date to today

    then insert the new pricelist with effectivefrom of today, effectiveto NULL

    thus, you will now have --
    Code:
    product price effectivefrom effectiveto
     widget  9.37   2005-01-15   2005-02-28
     widget 10.00   2005-02-28     null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    I want my 4th arrow! garlinto's Avatar
    Join Date
    Jun 2002
    Location
    Riding the electron wave
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you r937. Your solution is an answer to something I have struggled with since I began writing this invoicing system.

    Right now, I store the invoice_items in a so-named table, where I include the price and the invoice_id. Now, I really don't need to include the price, since the "retired" record will still exist in the pricelist table with the appropriate timestamp.

    Now that I have that straight, I need some help with the UPDATE join.
    First I need to update all of the existing records and set their effectiveto value to today's date.Am I on the right track with this:
    Code:
    update pricelist pl
        set pl.effectiveto = curdate()
        left
      	join temp_pricelist tpl
        on pl.item_id = tpl.item_id
        where pl.effectiveto = NULL
    Now, for the insert, should I just export the contents of temp_pricelist as SQL and do each insert one line at a time? What would you suggest?
    Ducharme's Axiom: "If you view your problem closely
    enough, you will recognize yourself as part of the problem."


  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    for the update, i've only ever used the version 4.0 multi-table update syntax, which still uses table list syntax (as opposed to JOIN syntax), and have never experimented with an outer join in an update -- but you wouldn't want to anyway, i mean, if the table you are updating has a row that doesn't match the new data, what is there to update?

    you may want to do the following in two separate steps, to examine the results after each one has run, and don't forget to take a backup first --
    Code:
    update pricelist 
         , temp_pricelist
       set pricelist.effectiveto = current_date
     where pricelist.item_id
         = temp_pricelist.item_id;
    Code:
    insert 
      into pricelist
         ( item_id
         , price
         , effectivefrom
         , effectiveto
         )
    select item_id
         , price
         , current_date    
         , null
      from temp_pricelist;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •