CODEWITHSUNDEEP
mysql
mmdel
mmdel

Reputation: 1279

how to update one table with results from another mysql query

i am seeking help on updating one column in a table with results from another query.

table names:

saleitems - fields are (itemid,quantity,saledate) 
stockbuffers - fields are (itemid,bufferquantity)

i am getting the buffer quantity with the following query from sale items table:

SELECT (SUM(saleitems.quantity / 6)) * 6 FROM saleitems WHERE saleitems.saledate BETWEEN date_sub(curdate(),interval 180 day) AND NOW() GROUP BY saleitems.itemid

i want to update the column buffer quantity from the above query's result. can anyone help. greatly appreaciate.

Upvotes: 1

Views: 378

Answers (1)

heretolearn
heretolearn

Reputation: 6545

You can use update query as:

Update stockbuffers set bufferquantity =(SELECT (SUM(saleitems.quantity / 6)) * 6 FROM saleitems WHERE saleitems.saledate BETWEEN date_sub(curdate(),interval 180 day) AND NOW() GROUP BY saleitems.itemid)

its just a sample and not have been tested.

Upvotes: 1

Related Questions