Duplicate Product Entries IDs in Workarea

  • Updated

In some cases sync collisions can occur causing duplicate product entries to show in the workarea.

See the following example:

  1. To prevent the issue from occuring you will need to start by resolving sync collisions for price entries. Go into the workarea > settings > synchronization > settings page and check off the price entries checkbox. Do this on the sending and receiving servers. 

  2. To list all the products that have multiple prices defined, run the following query:
    select c.content_id, c.content_title, 
    (select cft.FolderPath from content_folder_tbl cft where cft.folder_id = c.folder_id)
    from content c
    where content_id in (
    select entryid from ecom_ProductPriceListView pplv group by entryid, CurrencyId having count(*) > 1
    )

  3. To clean up old price entries for products that have duplicate entries, run the following query (back up the database before running this, and validate that the results are correct before moving forward)
    DELETE entry_price_tbl
    FROM   
    (
    select row_number() over (partition by entryid, currencyid order by datemodified desc) as rowid, * from
    (
    select priceid, entryid, CurrencyId,
    (select pt.date_modified from price_tbl pt where pt.priceid = vw.priceid) as datemodified,
    (select pt.date_created from price_tbl pt where pt.priceid = vw.priceid) as datecreated
    from ecom_ProductPriceListView vw where entryid in(
    select entryid from ecom_ProductPriceListView group by entryid, CurrencyId having count(*) > 1
    ) 
    ) list
    ) ranked
    INNER JOIN entry_price_tbl
    ON ranked.rowid > 1
    AND ranked.EntryId = entry_price_tbl.EntryId
    AND ranked.PriceId = entry_price_tbl.PriceId