(Solved) Can deleted items be removed?
I was playing around with item importing and would like to remove a bunch (some 45,000) items from my deleted items. I tried cleanup old items with no success - the items persist. How do I remove these extraneous items?
Comments
16 comments
You would have to execute sql statements to remove items. NOTE: If there are any sales this will break them as they wouldn't reference any items anymore.
You would need to use phpMyAdmin or other tool if you are using download version. If you are using cloud verison you would need to open a ticket
SET FOREIGN_KEY_CHECKS=0;
TRUNCATE TABLE phppos_items;
TRUNCATE TABLE phppos_inventory;
TRUNCATE TABLE phppos_inventory_counts_items;
TRUNCATE TABLE phppos_items_taxes;
TRUNCATE TABLE phppos_item_kits;
TRUNCATE TABLE phppos_item_kit_items;
TRUNCATE TABLE phppos_item_kits_tier_prices;
TRUNCATE TABLE phppos_item_kits_taxes;
TRUNCATE TABLE phppos_location_items;
TRUNCATE TABLE phppos_location_items_taxes;
TRUNCATE TABLE phppos_location_items_tier_prices;
TRUNCATE TABLE phppos_location_item_kits;
TRUNCATE TABLE phppos_location_item_kits_taxes;
TRUNCATE TABLE phppos_location_item_kits_tier_prices;
SET FOREIGN_KEY_CHECKS=1;
Chris, thank you for your response. What purpose does the Cleanup Old Items serve? I was under the impression that it would remove the Item IDs for use again.
It removes the UPC + Product ID from deleted items so they can be re-used
One last question, does your SQL query remove all items or just deleted items?
It removes all items. If you want jus deleted you would need to only run these queries
SET FOREIGN_KEY_CHECKS=0;
DELETE FROM phppos_items WHERE deleted = 1
Do I need to
SET FOREIGN_KEY_CHECKS=1;
after the two other queries?
no
I assume this query:
SET FOREIGN_KEY_CHECKS=0;
DELETE FROM phppos_items WHERE deleted = 1
does not remove images assigned to the original item? I ran it to remove over 3800 deleted items (which it did) but database size export went from 2.5G to 2.49G. So I assume those 3800 images are still embedded in db?
Is there a way to delete the images along with the item?
they are in phppos_app_files table and also referenced in phppos_item_images
But be careful not to delete all as active items are in there too
I restored the database with the copy I made before removing. Is there a way to run the query that will take each deleted item and it's associated image out in one fell swoop?
I don't recommend running the following; but the others should be fine. The reason is that past reporting will be messed up.
DELETE FROM phppos_items WHERE deleted = 1;
RUN THE FOLLOWING TO CLEANUP IMAGES
SET FOREIGN_KEY_CHECKS=0;
DELETE FROM phppos_app_files WHERE file_id IN(SELECT image_id FROM phppos_item_images WHERE item_id IN (SELECT item_id FROM phppos_items WHERE `deleted` = 1));
DELETE FROM phppos_item_images WHERE item_id IN (SELECT item_id FROM phppos_items WHERE `deleted` = 1);
Ohh! I didn't think about the reporting enough I think. Will it break financial reports and everything? I understand that it would mess up reports containing the specific item but would historical sales figures also change?
Sweet! The second query set worked like a charm! left the items so that the reporting isnt broken but removed the images from them and knocked over 400M off my database! Thank you!
Hello, I am trying also to clear all deleted products but the following SQL request doesn't seem to work anymore :
SET FOREIGN_KEY_CHECKS=0;
DELETE FROM phppos_items WHERE deleted = 1
What should I do to really empty all deleted products ?
Thanks
Are you running this from phpMyAdmin? Make sure foreign key checks in unchecked there also
I RAN THE CODE ON SQL AND I GOT THE BELOW RESULT
MySQL said:
#1701 - Cannot truncate a table referenced in a foreign key constraint (`kbconcep_phppos`.`phppos_additional_item_numbers`, CONSTRAINT `phppos_additional_item_numbers_ibfk_1`)Please sign in to leave a comment.