Deleting assets or whole locations

To delete a single asset and/or location in the United Manufacturing Hub, you should execute these SQL commands.

2 minute read

Instructions

There is no UI yet to do it, so you must use SQL to do it:

Executing SQL commands in the United Manufacturing Hub?

To delete one or multiple assets, you can use the following base template:

BEGIN;

WITH assets_to_be_deleted AS (SELECT id FROM assettable WHERE location='ABC')
DELETE FROM shifttable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted);

WITH assets_to_be_deleted AS (SELECT id FROM assettable WHERE location='ABC')
DELETE FROM counttable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted);

WITH assets_to_be_deleted AS (SELECT id FROM assettable WHERE location='ABC')
DELETE FROM ordertable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted);

WITH assets_to_be_deleted AS (SELECT id FROM assettable WHERE location='ABC')
DELETE FROM processvaluestringtable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted);

WITH assets_to_be_deleted AS (SELECT id FROM assettable WHERE location='ABC')
DELETE FROM processvaluetable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted);

WITH assets_to_be_deleted AS (SELECT id FROM assettable WHERE location='ABC')
DELETE FROM producttable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted);

WITH assets_to_be_deleted AS (SELECT id FROM assettable WHERE location='ABC')
DELETE FROM shifttable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted);

WITH assets_to_be_deleted AS (SELECT id FROM assettable WHERE location='ABC')
DELETE FROM statetable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted);

-- the following commands have not been tested yet and will not be executed by default. Please use with caution and only if you used the track&trace feature of UMH

--WITH assets_to_be_deleted AS (SELECT id FROM assettable WHERE location='onur'), uniqueproducts_to_be_deleted AS (SELECT uniqueproductid FROM uniqueproducttable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted))
--DELETE FROM producttagtable WHERE product_uid IN (SELECT uniqueproductid FROM uniqueproducts_to_be_deleted);

--WITH assets_to_be_deleted AS (SELECT id FROM assettable WHERE location='onur'), uniqueproducts_to_be_deleted AS (SELECT uniqueproductid FROM uniqueproducttable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted))
--DELETE FROM producttagstringtable WHERE product_uid IN (SELECT uniqueproductid FROM uniqueproducts_to_be_deleted);

--WITH assets_to_be_deleted AS (SELECT id FROM assettable WHERE location='onur'), uniqueproducts_to_be_deleted AS (SELECT uniqueproductid FROM uniqueproducttable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted))
--DELETE FROM productinheritancetable WHERE parent_uid IN (SELECT uniqueproductid FROM uniqueproducts_to_be_deleted) OR child_uid IN (SELECT uniqueproductid FROM uniqueproducts_to_be_deleted);

--WITH assets_to_be_deleted AS (SELECT id FROM assettable WHERE location='onur')
--DELETE FROM uniqueproducttable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted);

-- this will be again executed

WITH assets_to_be_deleted AS (SELECT id FROM assettable WHERE location='ABC')
DELETE FROM assettable WHERE id IN (SELECT id FROM assets_to_be_deleted);

COMMIT;

Please adjust the line WHERE location='ABC' to what you want to delete. The above template will delete all assets within that location for all customers.

To delete only a specific asset you can use the following example: WHERE assetname='XYZ' AND location='ABC' AND customer='factoryinsight'

Last modified February 17, 2023: update (#208) (ea731fc)