Find the answer to your Linux question:
Results 1 to 9 of 9
Code: UPDATE product, SET so what i need to do is here is an if command "IF price > 75 THEN price = price *1.20 END IF But i cant ...
Enjoy an ad free experience by logging in. Not a member yet? Register.
  1. #1
    Linux Newbie
    Join Date
    May 2009
    Location
    Kitchener, Ontario, Canada
    Posts
    206

    SQL Oracle Help


    Code:
    UPDATE product, 
        SET
    so what i need to do is here is an if command "IF price > 75 THEN price = price *1.20 END IF But i cant seem to get the right syntax :S
    Code:
    WHERE vendor_id IN (SELECT product_id, vendor_id, price
                            FROM product
                            WHERE vendor_id IN (SELECT vendor_id
                                                FROM vendor
                                                WHERE name = 'SyQuest Technology'))  
    ROLLBACK;
    Last edited by donaldfarkas; 10-08-2010 at 08:05 PM. Reason: wrong title

  2. #2
    ved
    ved is offline
    Linux User ved's Avatar
    Join Date
    Jan 2008
    Location
    New Delhi
    Posts
    309
    hi
    it can be done without if then else,
    update p..
    set price=price*1.2
    where price >75
    and vendor_id in ?
    try it if u deem it necessary..
    Take risks: if you win, you will be happy; if you lose, you will be wise.
    New Users : Read This First

  3. #3
    Linux Newbie
    Join Date
    May 2009
    Location
    Kitchener, Ontario, Canada
    Posts
    206
    Quote Originally Posted by ved View Post
    hi
    it can be done without if then else,
    update p..
    set price=price*1.2
    where price >75
    and vendor_id in ?
    try it if u deem it necessary..
    great i think that would work i dont know why i didnt come up with that :S gonna give it a try on tuesday ill let you know how it works I dont see why it wouldnt

  4. $spacer_open
    $spacer_close
  5. #4
    Linux Guru Rubberman's Avatar
    Join Date
    Apr 2009
    Location
    I can be found either 40 miles west of Chicago, in Chicago, or in a galaxy far, far away.
    Posts
    11,665
    Code:
    update product set price=price*1.20 where price>75 and vendor_id in
        (select vendor_id from vendor where name = 'SyQuest Technology');
    
    or
    
    update product set price=price*1.20 where vendor_id in
        (select vendor_id from vendor where name = 'SyQuest Technology')
        and price>75;
    In theory, the optimizer should create the same plan for both statements, but not necessarily, so the second may be better in that it will perform the vendor_id restriction first (once) instead of each time it finds a candidate entry where the price > 75. If you are using Oracle, you can ask it to "explain plan" for each and compare the cost of each version of the query.
    Sometimes, real fast is almost as good as real time.
    Just remember, Semper Gumbi - always be flexible!

  6. #5
    Linux Engineer Kloschüssel's Avatar
    Join Date
    Oct 2005
    Location
    Italy
    Posts
    773
    i would use the update join syntax:

    Code:
    update product as p
     inner join vendor v using(vendor_id)
    set price = price * 1.2
    where v.name = 'SyQuest Technology' and price > 75

  7. #6
    Linux Guru Rubberman's Avatar
    Join Date
    Apr 2009
    Location
    I can be found either 40 miles west of Chicago, in Chicago, or in a galaxy far, far away.
    Posts
    11,665
    Quote Originally Posted by Kloschüssel View Post
    i would use the update join syntax:

    Code:
    update product as p
     inner join vendor v using(vendor_id)
    set price = price * 1.2
    where v.name = 'SyQuest Technology' and price > 75
    That may works too, though the syntax isn't supported by all SQL implementations. I think this will work more universally:
    Code:
    update product p, vendor v set p.price = p.price * 1.2
       where p.vendor_id = v.vendor_id and
       v.name = 'SyQuest Technology' and p.price > 75;
    Again, since I haven't tried this, not all implementations may support it; however, my first example should work on all known SQL implementations. Anyway, he can try them all manually and assuming his implementation supports transactional boundaries, just rollback after trying each one.
    Sometimes, real fast is almost as good as real time.
    Just remember, Semper Gumbi - always be flexible!

  8. #7
    Linux Newbie
    Join Date
    May 2009
    Location
    Kitchener, Ontario, Canada
    Posts
    206
    Thank you guys for all your input ill give it a try on tuesday and let you know how i did it

  9. #8
    ved
    ved is offline
    Linux User ved's Avatar
    Join Date
    Jan 2008
    Location
    New Delhi
    Posts
    309
    i think first you have to try it then discuses .
    Take risks: if you win, you will be happy; if you lose, you will be wise.
    New Users : Read This First

  10. #9
    Linux Newbie
    Join Date
    May 2009
    Location
    Kitchener, Ontario, Canada
    Posts
    206
    Quote Originally Posted by ved View Post
    i think first you have to try it then discuses .
    I just appreciate everyone taking their time and giving their input.

Posting Permissions

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