php - Update a table sales with values from table products with WHERE clause in both Update and select query

546

My aim is to get values from table products to table sales where sales.Product= products.Productname. This works well. with the code below .

UPDATE sales
SET Amount=(SELECT  Selling_Price FROM  products WHERE sales.Product= products.Productname);
        ;

My challenge now is how to update only where sales.Amount= 0. While running this code. I want Column Amount with values ' != 0 ignored .

458

Answer

Solution:

[...] how to update only wheresales.Amount= 0. I want columnAmount with values != 0 ignored .

Just add awhere clause:

UPDATE sales
SET Amount = (SELECT Selling_Price FROM products WHERE sales.Product = products.Productname)
WHERE Amount <> 0;
67

Answer

Solution:

If you only want to update rows whereamount = 0, then you would addwhere clause to theupdate:

UPDATE sales
    SET Amount = (SELECT p.Selling_Price
                  FROM products p
                  WHERE sales.Product = p.Productname
                 )
    WHERE Amount = 0;

IfAmount could beNULL and you want those rows updated as well, you would asOR Amount IS NULL.

People are also looking for solutions to the problem: php - How to apply a where condition on average function results without second select?

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.