Updating stocks quantity in Product Table from Cart

Hello everyone so basically I have this 2 tables

ProductLists

and

Cart

Note: Cart table contains temporary datas inside it, if the orders inside cart table are already paid the table itself will truncate.

I tried running this query

UPDATE productlists
SET stocks = stocks - (SELECT SUM(QTY) FROM cart WHERE ID = productlists.product_id)

and this happened output

any advice on how to do this without making the stocks of the other products go Null? Thank you very much

Answer

Try this for MSSQL:

UPDATE productlists
SET stocks = stocks - Q.QTY
FROM (
    SELECT 
        ID,
        SUM(QTY) as QTY
    FROM 
        cart 
    GROUP BY
        ID
    ) Q
WHERE productlists.product_id = Q.ID

For phpMyAdmin:

update productlists set stocks = stocks - (select sum(QTY) from cart where ID = productlists.product_id) where product_id in (select ID FROM cart);