phpmyadmin - SQL: subtracting value in a table from another table

Question

First time question here. I'm trying to subtract the stock in table SS_Products by a quantity in EXPOS.INV. This is the code that I have tried:

UPDATE SS_products SS 
SET SS.nb_stock = (SELECT SS.nb_stock - EX.Qty 
                   FROM EXPOS_INV EX
                   WHERE  SS.product_code = EX.Barcode)

The code works, it updates the nb_stock where the lines match between the 2 tables, but it nulls it on every other line.

I tried adding an Inner Join:

UPDATE SS_products SS 
SET SS.nb_stock = (SELECT SS.nb_stock - EX.Qty 
                   FROM EXPOS_INV EX
                   WHERE SS.product_code = EX.Barcode)
INNER JOIN EXPOS_INV ON SS_products.product_code = EXPOS_INV.Barcode

This one doesn't go through. I also tried with a WHERE but it doesn't take any clause from EXPOS_INV

Following an answer, I tried the Where Exists but it returned a bunch of truncate messages for unrelated lines.

I followed a comment for a basic example, and I came up with this code

UPDATE 
    SS_products
SET 
    SS_products.nb_stock = (Select SS.nb_stock - EX.Qty from EXPOS_INV EX
                   Where SS.product_code = EX.Barcode)
FROM 
    SS_products
    INNER JOIN EXPOS_INV ON SS_products.product_code = EXPOS_INV.Barcode
Where
    SS_products.product_code = EXPOS_INV.Barcode

this returns a syntax error.

Answer 1

You can check using where if the matching row exists:

UPDATE SS_products SS 
    SET SS.nb_stock = (Select SS.nb_stock - EX.Qty
                       from EXPOS_INV EX
                       Where SS.product_code = EX.Barcode
                      )
    WHERE EXISTS (Select SS.nb_stock - EX.Qty
                  from EXPOS_INV EX
                  Where SS.product_code = EX.Barcode
                 );