Home > Blog > How to Clean Up Data in P21 Cloud-Hosted Environments [+ Example SQL Queries to Steal]

Blog

How to Clean Up Data in P21 Cloud-Hosted Environments [+ Example SQL Queries to Steal]

Is your Prophet 21 data starting to look like a box of tangled holiday lights? Let’s get those records sorted, plus some sample SQL queries from our team.

September 11, 2025
Katie Harding

Epicor Prophet 21’s Mass Update function is a powerful tool for fixing data quality issues in large record sets. Clean data leads to better reports and clearer insights for distributors.

Before Performing a Mass Update in Prophet 21

Conveyance Solutions recommends using a logic tree to identify whether a record is orphaned, incomplete, or duplicated, then applying targeted cleanup steps for each issue.

Data cleanup should be handled by an IT admin with input from business stakeholders to avoid future headaches and ensure accuracy.

Identify What Data Needs to be Cleaned Up

Duplicated, incomplete, or orphaned records compromise data integrity and reporting accuracy.

Using SQL queries is one of the easiest ways to identify what records to update or delete
Check the size and impact of each problem before choosing the right cleanup approach

2 Different Methods to Update Records in P21

Epicor Prophet 21 offers two main ways to update records: the Basic Mass Update and the Excel Query Method.

Basic Mass Update Method

The Basic Mass update is best for updating a single field in batches of up to 500 records. Avoid using this method for large datasets (over 1,000 records) to prevent slow performance or server issues, and to save your sanity.

Use Find and Replace to update values across filtered records.
Preview shows the first 30 records that match your criteria before making changes.
Select only the columns you need to update.
Changes are only applied when Send Changes is selected. You can also choose Schedule Changes to run updates later.

Excel Query Method

The Excel Query method is used for complex edits or larger batches—over 1,000 records or changes to multiple fields. This method is ideal for transforming large datasets that go beyond what basic Mass Update allows.

Select columns, run the query, and export results to Excel for offline changes.
Avoid editing ID or Save Changes columns—modifying these can corrupt data or crash the system.
Import the edited file, confirm columns to be updated, and press Send Changes to apply updates now or Schedule Changes to run updates later.

How to Use Mass Update in Epicor Prophet 21: Scenarios

How to Clean Up Duplicated Item & Customer Records

Duplicated entries are records that share overlapping details in categories like items, customers, vendors, or suppliers. P21 also has certain limitations on merging records, only supporting Item and Customer Merge.

First, select the “winning” record you want to move forward with and merge details from other duplicates as needed with the mass update.
If merging is not possible, delete the losing records only after transferring the required data points.

How to Clean Up Duplicated Supplier Records

Suppliers are considered enterprise data points and are used across locations and companies within P21.

Identify the “winning” supplier record to keep
Confirm if any data from the “loser” record should be added to the “winner” record
Perform the Mass Update to add those data points to the “winner”
Delete the “loser” record

How to Resolve Incomplete Records

Missing data creates reporting problems and often snowballs into worse issues.

Decide what’s missing and if that information can be found and added to the record.
Update the record if it should be kept; otherwise, delete it.

How to Manage Orphaned Records

Orphaned records are often the trickiest records to update. The core issue of orphaned records is that they have no ties to other records. This can happen when contacts are not associated with a customer or supplier.

First, determine if these records should be updated to restore relationships or deleted outright.
Gather the necessary information to complete the updates.
Delete the unnecessary records after they are restored elsewhere or deleted outright.

Steal our SQL Queries for Prophet 21 Data Clean Up

Ready-made query templates speed up mass updates and make recurring tasks easier.

Find Customers Who Have the Same Normalized Names:

This query searches for customers who may have duplicate records.

				
					SELECT 
    normalized_name, 
    STRING_AGG(customer_name, ', ') AS similar_customers,
    STRING_AGG(CAST(customer_id AS varchar), ', ') AS customer_ids
FROM (
    SELECT 
        customer_id,
        LTRIM(RTRIM(
            REPLACE(REPLACE(REPLACE(REPLACE(LOWER(customer_name),
            ' incorporated', ''), 
            ' inc', ''), 
            ' company', ''), 
            ' co', '')
        )) AS normalized_name,
        customer_name
    FROM customer
) x
GROUP BY normalized_name
HAVING COUNT(DISTINCT customer_id) > 1;

Find Vendors Who Have the Same Normalized Name:

This query searches for vendors who might be duplicated.

				
					SELECT 
    normalized_name, 
    STRING_AGG(vendor_name, ', ') AS similar_vendors,
    STRING_AGG(CAST(vendor_id AS varchar), ', ') AS vendor_ids
FROM (
    SELECT 
        vendor_id,
        LTRIM(RTRIM(
            REPLACE(REPLACE(REPLACE(REPLACE(LOWER(vendor_name),
            ' incorporated', ''), 
            ' inc', ''), 
            ' company', ''), 
            ' co', '')
        )) AS normalized_name,
        vendor_name
    FROM vendor
) x
GROUP BY normalized_name
HAVING COUNT(DISTINCT vendor_id) > 1;

Find “Bad” Characters in Item IDs and Descriptions:

				
					SELECT item_id, item_desc, coalesce(extended_desc, '') as extended_desc
FROM inv_mast
WHERE 1=1
and delete_flag = 'N'
and item_id LIKE '%[;:''"`%]%'
   OR item_desc LIKE '%[;:''"`%]%'
   OR extended_desc LIKE '%[;:''"`%]%';

Best Practices When Performing a Mass Update

Careful planning protects system integrity and supports long-term growth. Here’s what works for our team:

Review before deleting: Always check data before deletion to avoid losing important info.
Preview data and set clear filters: Use the preview feature and reset filter criteria to validate any changes made.
Standardize entries: Address missing values, correct data entry errors, and ensure consistent usage of all fields. For example, use standard address formats and set rules for data entry.
Audit regularly: Schedule regular reviews and cleanups to maintain data consistency.
Assign ownership: Define roles for data management across your organization to maintain long-term data health. Every user, from executive to analyst, plays a part in keeping data clean and actionable.

Leave Data Cleanup to Us

Data clean-up takes time most teams don’t have. Conveyance Solutions uses a proprietary tool that leverages confidence scores based on the Levenshtein distance to find and fix duplicates fast, keeping operations on track. 

For fast, reliable data clean-up backed by true distribution expertise, chat with our experts today to get the guidance needed to keep your Prophet 21 system running smoothly.