CREATE TABLE product(
pid char(2),
pname varchar(10),
quan int);

INSERT INTO product(pid, pname, quan)
VALUES 
('P1', 'Bread', 10),
('P2', 'Milk', 20),
('P3', 'Sugar', 30),
('P4', 'Tea', 40);

CREATE TABLE sales (
pid char(2),
quan int);

INSERT INTO sales(pid, quan)
VALUES
('P1',1),
('P2',2),
('P3',3),
('P4',4),
('P1',1),
('P2',2),
('P3',3),
('P4',4);

DROP TABLE sales;
DROP TABLE product;

UPDATE product p, (SELECT pid, SUM(quan) abc FROM sales GROUP BY pid) s
SET p.quan = p.quan - s.abc
WHERE p.pid = s.pid;