Thread: Simple Database Script Help

We are using ML 8.0.1.2

1. We want to use this plugin from ecommerce cart mods to increase the prices in-bulk, a couple of our categories: http://www.ecommercecartmods.com/p-4...torefront.aspx

2. But the plugin changes price based on the manufacturer (not the category), and we don't currently use the Manufacturer field

3. Can anyone provide a simple script for SQL Server 2005 that says, for all categories = 'necklaces', change manufacturer to 'Smith'

4. Then we can use this same script to change all the other categories to their appropriate manufacturers as well.

5. Any help would be greatly appreciated.

How about something like this:-

C#/VB.NET Code:

UPDATE ProductVariant SET Price = Price * 1.2 WHERE ProductID IN (SELECT ProductID FROM ProductCategory WHERE CategoryID = 123); 


1.2 - to give a 20% increase.
123 - for CategoryID 123.




Alternatively - something like this will set Manfacturer for a specific category

C#/VB.NET Code:

UPDATE ProductManufacturer SET ManufacturerID = 222 WHERE ProductID IN (SELECT ProductID FROM ProductCategory WHERE CategoryID = 123); 


I tried it, but I ran into the issues below

1.) I tried this on a Sub-category and a Top Category (one with no parent) and this is what happened.

On The Sub-Category:

UPDATE ProductVariant SET Price = Price * 1.1 WHERE ProductID IN (SELECT ProductID FROM ProductCategory WHERE CategoryID = 338);

Error listed below:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.


--------------------------------------------------------------

2.) Top the Top-level Category listed below:

UPDATE ProductVariant SET Price = Price * 1.1 WHERE ProductID IN (SELECT ProductID FROM ProductCategory WHERE CategoryID = 235);

Error for Top Level listed Below:

(0 row(s) affected)

This should have affected a lot of rows.


3.) Any ideas what I did wrong?

Not sure about the error message you get for 1, where exactly are you running the query?

But - for number 2, do you actually have any products mapped to 235? Can you try running just this :-

C#/VB.NET Code:

SELECT ProductID FROM ProductCategory WHERE CategoryID = 235 


TTFN

BFG

I think we're making progress. But I need a little more help.

1. I was able to successfully change the Manufacturer to 222 for all products in category 362. I can see this in the database.

2. But the admin section of the site doesn't reflect the new 222 for the manufacturer id

3. Instead it shows --SELECT-- when it should show 222. Originally the Manufacturer showed Generic Mfg

The SQL I used is as follows:

UPDATE ProductManufacturer SET ManufacturerID = 222 WHERE ProductID IN (SELECT ProductID FROM ProductCategory WHERE CategoryID = 362);

4. How can I get the Manufacturer to show in the dropdown Manufacturer field after the change has been made?

If you actually have the correct value in the database - but not showing in admin - then it must be a caching issue.

Try resetting your cache - or touching web.config to restart the app.



TTFN

BFG

After overcoming various operator error issues (sorry about that) we got the first script to work . . .

UPDATE ProductVariant SET Price = Price * 1.2 WHERE ProductID IN (SELECT ProductID FROM ProductCategory WHERE CategoryID = 123);

. . . and we can increase prices by category.

But it only works for sub-categories.

1. How can I change prices for a top-level parent category and all sub-categories?

2. As it stands now I would have to run that same script many times for some of our larger categories that have many sub-categories.

If you also have your products mapped to the parent category, then you can just use the parent category ID - if not, then something like this will work :-

C#/VB.NET Code:

UPDATE ProductVariant SET Price = Price * 1.2 WHERE ProductID IN (SELECT ProductID FROM ProductCategory WHERE CategoryID IN (SELECT CategoryID From Category WHERE ParentCategoryID = 456) ); 


TTFN

BFG

you should try this plug in from from Vibe
http://www.vibecommerce.com/add-ons/...torefront.aspx
it have everything already build in it.