Thread: Update Price and Inventory

I would like to update price and inventory directly in the database. Can you point me to the appropriate tables?

If you are going to be updating prices and inventory directly from the database via SQL queries, please make sure that you make a full backup of the database first!

The tables for product price and inventory are located in dbo.ProductVariant, as Price and Inventory, respectively.

If you are defining extended prices, that table is located at dbo.ExtendedPrice.

Finally, if you are defining inventory that is tracked by size and color, that would be located at dbo.Inventory.

Thank you.

Hi - I'd like to be able to export an inventory list, for checking against my actual stock on a periodic basis.

Can you please tell me how to do this?

Thanks

Snowy,

You could create a custom report that would show the product name, sku and inventory by using this SQL command.

Code:

INSERT INTO CustomReport (Name, Description, SQLCommand) 
VALUES ('Inventory Report', 'Used for managing on hand inventory', 
'SELECT ProductID, Name,SKUSuffix,Inventory FROM ProductVariant WHERE Name!=''')

In this statement you clearly see I am Selecting the ProductID, Name, SKUSuffix, and Inventory columns from the ProductVariant table. The next statement I make is a WHERE, to filter out any Names that are not equal to empty. By adding different statements you can minipulate the data. For instance, if I changed my WHERE to

Code:

WHERE Inventory<=0

it would return a table of all products that are negative or zero in the inventory.

For more information on custom reports and how to use this command please see the manual page on Custom Reports