Can I globally correct negative Special prices after importing data from EasyPopulat?

[Tapper]

Hi

I have around 40,000 products and hundreds of items with a special price. Some items are permanent specials and others have expiration dates. Oddly enough, in the database some of the non-expiring ones show something like 01-01-0001 as the expiration date and others have a blank.

I enter the sale price as a dollar amount and upload using Easy Pop. This summer there have been several price increases so now the regular price is higher than the sale price and I have "Save -48%" all over the place.

Is there a way to fix this globally? Say, some SQL I can run? I noticed there are 2 specials tables, one with SKUs and the other with product IDs. The only thing I've been able to try is downloading the catalog, calculating differences and filtering for mis-matched special / regular price pairs. I'd prefer some database option so I can either delete the bad specials or adjust the numbers.

Thanks!

[chadderuski]

Hiya Tapper,

I can verify that the behavior you see for "blank" expiring dates is 01-01-0001 in the table. Don't know why ZC does that, but it does seem to be correct behavior.

When you import with EasyPopulate, and have a special price set higher than normal price, don't you get a warning like this:

SKIPPED! - Model: ROY 7714 - specials price higher than normal price...

In which case EP does not set the special, but skips the import.

This behavior should be correct for EP ver 1.2.5.4. Cannot speak for the others.

-cj

[Tapper]

Thanks Chadder!

Actually, I think the error happens when I update an item without it's special price. If I create a special manually or with EZ Pop, say a $20 item is a $10 special.

Then, in a later upload the actual price of the item drops to $5 and I just upload the price with no special columns I end up with a negative special.

Sometimes I do see that message but not in all cases. I think I might have to clear the whole specials table and start over.

[Paul69]

You could do it with a SQL statement like:

DELETE FROM blah
WHERE Special price > Price

Just remember that if you delete * it will delete the entire entry so specify what fields to delete exactly as needed. This will make the special null if you remove it. ALWAYS back up...

EP is not user friendly to remove data so simply setting the expiration dates to earlier should work. Try an import and make the specials expired... Probably the safe way to do it.

[Tapper]

You could do it with a SQL statement like:

DELETE FROM blah
WHERE Special price > Price

Yes, that would certainly be the way to go if the price was in the specials table. It's not. The only price in the Specials table is the specials price and product IDs, not even SKUs. It would take a wild Join to match the prod IDs against the prods table and then compare products.price to specials.special_price

[chadderuski]

Yes, that would certainly be the way to go if the price was in the specials table. It's not. The only price in the Specials table is the specials price and product IDs, not even SKUs. It would take a wild Join to match the prod IDs against the prods table and then compare products.price to specials.special_price

You will have to test this logic in your version of EP, but according to my code:

"If a null value in specials price, do not add or update. If price = 0, delete it"

This makes good sense. You can remove or even clear specific specials on a product by product basis.

I will have to test this code myself as i have not (until just recently) been asked to do specials.

Just tested EP with special set to Zero, will delete special!