0

Going to have an sql test in a few days. We can't use a computer so everything is written without its help.

I'm having some issues with this exercise.

The objective is to update the values of the table purchase using the values from the table product. If the product.production_date is highest than sold.purchase_date it's necessary to set the purchase_date to be equal to production_date.

Here's what I did:

update purchase
set purchase_date=product.production_date
from product
where purchase.purchase_date<product.production_date;

Can I do this? I mean, use the update and from commands together?

Thank you.

eyelash
  • 112
  • 1
  • 1
  • 11
  • 1
    Yes. No probs.!! – Strawberry Dec 16 '18 at 22:54
  • 2
    I removed the incompatible database tags. Please tag only with the database you are really using. Also, your query makes no sense. Sample data, desired results, and an explanation of what you are doing would all help. – Gordon Linoff Dec 16 '18 at 22:55
  • 2
    Shouldn't the purchased and the produced product be linked somehow, like sharing a common ID? I'm not sure your query does what you want... – sticky bit Dec 16 '18 at 23:58

3 Answers3

1

Yes, you can. I think it was a typo: you wrote updade instead of update.

Mohammad Yasir Arafat
  • 655
  • 4
  • 13
1

Please try below query by reassigning id with the common column for both table.

update purchase
set purchase_date = (SELECT product.production_date 
                   from product 
                  where purchase.id = product.id)
WHERE EXISTS (SELECT 1
          FROM product
          WHERE purchase.id = product.id
            and purchase.purchase_date < product.production_date);
Nick
  • 117
  • 1
  • 9
-1

You have to write an inner query to make it work else you may get an incorrect SQL error. Ex:

update purchase pur
set pur.purchase_date=(select pro.production_date
from product pro
where pur.purchase_date<pro.production_date);

For more details check: Update statement with inner join on Oracle

Mohit Garg
  • 1
  • 5