I have 2 tables having same structure in mysql database and want to find the difference of records to update my old table B. For this i am trying to do it in below 3 queries. Is there any fast and better approach doing same by multiple or one query? Table A is having new records and B having old.
- Find new records in A:
SELECT A.merchant_inventory_id FROM table1 A LEFT JOIN table2 B ON (A.model_no=B.model_no AND A.merchant_code=B.merchant_code AND A.manufacturer_name=B.manufacturer_name AND A.price=B.price AND A.p_and_p_charges=B.p_and_p_charges AND A.stock_availability=B.stock_availability) WHERE B.model_no is NULL
- Select records from table B which are not in A to be removed from B. Below is only select query:
SELECT B.merchant_inventory_id FROM table1 A LEFT JOIN table2 B ON (A.model_no=B.model_no AND A.merchant_code=B.merchant_code AND A.manufacturer_name=B.manufacturer_name AND A.price=B.price AND A.p_and_p_charges=B.p_and_p_charges AND A.stock_availability=B.stock_availability) WHERE A.model_no is NULL
- Find records having not same price or stock availability, These I will use to update table B:
SELECT A.merchant_inventory_id FROM table1 A LEFT JOIN table2 B ON (A.model_no=B.model_no AND A.merchant_code=B.merchant_code AND A.manufacturer_name=B.manufacturer_name AND A.p_and_p_charges=B.p_and_p_charges) WHERE A.price!=B.price OR A.stock_availability!=B.stock_availability
This way i will have same records in both tables which i can achieve by using replace into as well but i don’t want to use as i have to manage old and new dates and don’t want to replace each record by new record.
Is there any way i can do same by one query having select, delete and update or any other better approach if it is not the right way of doing?