select y.item_id,x.item_code,y.warehouse_id,x.total_bal as stockbal, 
sum(y.quantity) as phyqty 
from 0p4_item_physical_quantity y 
left join (SELECT *, sum( (ifnull(`in`,0))- (ifnull(`out`,0))) as 'total_bal' 
FROM `0p4_item_transaction` `tran` GROUP BY `item_id`,`warehouse_id`) x 
on x.item_id=y.item_id 
and x.warehouse_id=y.warehouse_id GROUP by y.item_id,y.warehouse_id 
HAVING phyqty!=total_bal

update 0p4_item_physical_quantity p
left join (select y.item_id,x.item_code,y.warehouse_id,x.total_bal as stockbal, 
sum(y.quantity) as phyqty 
from 0p4_item_physical_quantity y 
left join (SELECT *, sum( (ifnull(`in`,0))- (ifnull(`out`,0))) as 'total_bal' 
FROM `0p4_item_transaction` `tran` GROUP BY `item_id`,`warehouse_id`) x 
on x.item_id=y.item_id 
and x.warehouse_id=y.warehouse_id GROUP by y.item_id,y.warehouse_id 
HAVING phyqty!=total_bal) t on t.item_id=p.item_id and p.warehouse_id=t.warehouse_id
set p.quantity=t.stockbal

SELECT count(*) as c,warehouse_id,rack_id,item_id FROM 0p4_item_physical_quantity
group by warehouse_id,item_id
having c>1