CODEWITHSUNDEEP
sqljoinsql-update
eyelash
eyelash

Reputation: 112

SQL update table using values from another table

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.

Upvotes: 0

Views: 62

Answers (3)

Mohit Garg
Mohit Garg

Reputation: 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

Upvotes: -1

Nick
Nick

Reputation: 127

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);

Upvotes: 1

Mohammad Yasir Arafat
Mohammad Yasir Arafat

Reputation: 705

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

Upvotes: 1

Related Questions