Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2009
    Posts
    5

    Unanswered: update query mulitple if's

    Hi,

    I am needing to run a price update on over 6,000 products. Each product has a pricing criteria attached in the form of a letter (i.e A = 20% ).

    I need to be able to create the query to update the prices. for example

    if product2 has the letter C pricing criteria then add 25% to listprice

    there are 12 pricing categories in total.

    Please ask me questions, if i have not explained it very well and thankyou for any help in advance

    Craig

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    quickest and dirtiest...
    make a temporary table with the price code and the percentage increase
    then do an update query using a join or where clause

    eg
    Code:
    update myproducts set ProdCost=ProdCost * (1+MyTempTable.PercentageIncrease) where myproducts.pricecategory = mytemptable.pricecategory
    you could check that before running as an update by doing something similar just to make sure your query is doing what you expect

    Code:
    select *,ProdCost * (1+MyTempTable.PercentageIncrease)  as new prodcost from myproducts, mytemptable where myproducts.pricecategory = mytemptable.pricecategory

    but your percentage increase should be expressed as .xx ie .2 = 20%, .01=1%
    whatever you do do take a backup before runn ing the query, delete the temporary table and the query after you have run it and proved the values are correct. or if you want to leave the query hanging around for future use consider setting the percentage values to 0 after the query has run.. that stops an increase on an increase.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would not use If. Use a table, pop your update percentages in there (you can even go to town and do stuff like add timestamps) and update from there. This article is re SQL Server but 100% relevant:
    Data belongs in your tables -- not in your code
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    quickest and dirtiest...
    Au contraire - I would describe that as the cleanest and most flexible way to do this.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Mar 2009
    Posts
    5
    Hi guys, ok I have got round to creating this query however, when I view the data in the query designer it needs to insert a priceCode, it does not automattically run through them, here is the code. Is there anything I am missing

    Code:
    UPDATE sealyOnlineData SET pPrice = pPrice * (1+pricingTable.percentage)
    WHERE pricingTable.priceCode = sealyOnlineData.priceCode;
    The pricingTable has 2 fields, priceCode and percentage

    sealyOnlineData has 2 fields priceCode and pPrice

    pPrice needs to be update from the priceCode

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Look up INNER JOIN SQL in google
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Mar 2009
    Posts
    5
    Thank you for all your help, I have managed to get it working, here is the code for any future reference

    Code:
    UPDATE sealyOnlineData INNER JOIN pricingTable ON sealyOnlineData.priceCode = pricingTable.priceCode SET sealyOnlineData.pPrice = pricingTable.percentage * sealyOnlineData.pPrice
    WHERE (((pricingTable.priceCode)=[sealyOnlineData].[priceCode]));

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Note that your where clause is now redundant and can be removed.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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