Whats the best way to move data?
I'm using LOAD DATA INFILE to import an XML file into the `xml` table. This is a temporary table which is truncated before any data is loaded. Once the data is loaded into the `xml` table, I want to move it into the `products` table. To do this I need to create 3 jobs; NewProduct, UpdateProduct, DeleteProduct.
NewProduct: This is when a new product is located in the temporary `xml` table which doesn't already exist in the `products` table. The new product is then inserted into the `products` table.
UpdateProduct: This is when a product is located in both the `xml` table and the `products` table, but the data does not match.
DeleteProduct: This is when a product exists in `products` table but not the `xml` table. The product need to be updated to disable the product from future use.
Now I'm not sure on the "best" way to do this which is why I'm here to ask for advice. So let's start with the NewProduct. This is what I've come up with so far and it appears to work.
INSERT INTO products (name,type,size,colour,sku,weight,instock,quantity,price,image1,image2,image3,image4,image5,image6,description,added,pushupdate)
SELECT name,type,size,colour,sku,weight,instock,quantity,price,image1,image2,image3,image4,image5,image6,description,NOW(),1
FROM xml WHERE sku NOT IN (SELECT sku FROM fds_products)
This is how I'm doing my DeleteProducts.
UPDATE products SET removed = NOW(), pushupdate = 1 WHERE sku NOT IN (SELECT sku FROM xml)
Are you above two methods okay?
Finally the UpdateProduct is where I get lost because I'm not sure how to go about searching for updates between the tables. Any suggestions?