SQL Oracle Help

[donaldfarkas]

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

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;

[ved]

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..

[donaldfarkas]

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

[Rubberman]

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.

[Kloschüssel]

i would use the update join syntax:

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

[Rubberman]

i would use the update join syntax:

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:

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.

[donaldfarkas]

Thank you guys for all your input ill give it a try on tuesday and let you know how i did it

[ved]

i think first you have to try it then discuses .

[donaldfarkas]

i think first you have to try it then discuses .

I just appreciate everyone taking their time and giving their input.