mysql - Subtracting value of a particular column where a value of particular column is same in both tables -

my db has 2 tables stock , damage.

stock table looks like

item_code, ss_no, item_name, rack_no, shelf_no, cold_storage, batch_no, qty, packing, expiry_date, mrp, purchase_price, selling_price, margin, formulation, stock_date, min_qty, ss_flag_id, ban_flag_id, sales_discount '1', 1, 'abzorb powder', 'a-1', ' ', ' ', '9086626', 18, 1, '2017-06-01', 87.00, 66.29, 87.00, 0.00, 'powder', '2015-05-11', 0, 0, 0, 0.0 

damage table looks below

damage_stock_date, invoice_no, invoice_date, dist_name, contact_no, item_code, item_name, batch_no, mfr_name, expiry_date, qty, damaged_qty, unit_price, unit_vat, unit_discount, sub_total, total_amount, remarks, ds_flag_id, packing '2015-06-19', '56', '2015-06-19', 'ganapati drugs', '', '0', 'saxim_', '1', '', '', 50, 10, 2.00, 5.00, 0.00, 21.00, 21.00, '', 0, 0 

if want select row stock value of item_name column in both tables, use

select * stock s item_name in ( select item_name damage); 

which job want.

now want subtract value of qty col in damage stock's qty col value of item_name both columns must same.

i think have use variables don't know how..

try using join:

select *, (s.qty-d.qty) available stock s left join      damage d on d.item_name=s.item_name d.qty not null 

example in sql fiddle.

explanation:

a left join table damage condition d.qty not null same thing. same using in (as in question). advantage that, can use columns of damage table in main query.

so can find available quantity (s.qty-d.qty)

edit:

for updating table:

update  stock s left join          damage d on d.item_name = s.item_name set     s.qty=(s.qty-d.qty)   d.qty not null