phpmyadmin Question posted in
The official documentation can be found here.

How can I perform this sql update using sql instead of using code? – Phpmyadmin

I have been able to select this data, using these two sql queries

enter image description here

Query 1:

SELECT article_id, amount_required, amount_sold FROM products_articles,sales WHERE sales.product_id = products_articles.product_id

Query 2:

SELECT * FROM articles

What I want to do, is go through the first table (with amount sold and required) (it’s fine that there are duplicate rows), and for each row in the table multiply the value of amount_sold and amount_required and then subtract that value from amount_in_stock where the ids match in the second table.

Example from the first row:
2 * 4 = 8, change amount_in_stock from 124 to 116.
And so on…

How can I do this using just sql?

Tags:

2

Answers


  1. 0 
    UPDATE A
SET 
    A.amount_in_stock =(S.amountSold * S.amount_required)- A.amount_in_stock 
FROM articles AS A
INNER JOIN
products_articles AS PA
ON PA.article_id= A.article_id
INNER JOIN  Sales AS S 
ON S.product_id=PA.product_id
    Login or Signup to reply.
  2. 0

    Please try this:

    Update articles a
inner join 
(
SELECT article_id, sum(amount_required) amount_required, sum(amount_sold )amount_sold FROM products_articles inner join sales on sales.product_id = products_articles.product_id
group by article_id
)b on a.article_id=b.article_id
set a.amount_in_stock=a.amount_in_stock-(amount_required*amount_sold )

    Since there could be multiple rows in product_articles and amount_sold I have used group by to sum the amounts.

    For SQLite please try this:

    Update articles 
set amount_in_stock=(SELECT  sum(amount_required) * sum(amount_sold )  FROM products_articles inner join sales on sales.product_id = products_articles.product_id
where products_articles.article_id=articles.article_id
group by article_id
)
where exists (SELECT  *  FROM products_articles inner join sales on sales.product_id = products_articles.product_id where products_articles.article_id=articles.article_id
)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search