Thread: SQL Query to Update Extended Prices

SQL Query to Update Extended Prices?? Does anyone know what this is? I tried the insert function for dbo.extendedpricing, didn't take affect tho... Thanks !. I have my spreadsheet with my variant id, and extended price, and product id etc, product id starts at a2, variant id is b2, and extended price is c2. thanksagain

The table ExtendedPrice expects the following:

Code:

INSERT INTO [AspDotNetStorefront].[dbo].[ExtendedPrice]
           ([ExtendedPriceGUID]
           ,[VariantID]
           ,[CustomerLevelID]
           ,[Price]
           ,[ExtensionData]
           ,[CreatedOn])
     VALUES
           (<ExtendedPriceGUID, uniqueidentifier,>
           ,<VariantID, int,>
           ,<CustomerLevelID, int,>
           ,<Price, money,>
           ,<ExtensionData, ntext,>
           ,<CreatedOn, datetime,>)

All columns have to have something except ExtensionData which can be NULL or blank. CreatedOn defaults to GETDATE() ie now, CustomerLevelID defaults to 0 and ExtendedPriceGUID defaults to NEWID(). It's not interested in ProductID.

So you'll need this in your spreadsheet:

Code:

="INSERT INTO ExtendedPrice VALUES (NEWID()," & B2 & ",0," & C2 & ",NULL,GETDATE())"

This is for v8.0.1.2 db. Not tried or tested. I urge you to try this in a test db first.

I'm on a 7 version ... :-\

That only means I don't know what the structure of the table is. So maybe someone else can chime in, unless you know what the structure is?

You can use this :-

C#/VB.NET Code:

="INSERT INTO dbo.ExtendedPrice (VariantID, CustomerLevelID, Price) values ("&B2&","&D2&","&C2&");" 


This assumes that you have your CustomerLevelID in D2.

The query you need to run is as follows :-

INSERT INTO dbo.ExtendedPrice (VariantID, CustomerLevelID, Price) values (333,6,1.3254);

This inserts an extended price of 1.3254 for Customer Level 6 & VariantID 333



The other fields in the table (GUID,Date) will be auto populated.


TTFN

BFG

Whew thank you bfg, and yes i do have customer level id im going to run that in a little bit with one of my records, thanks ill let you know

Ahhh BFG I just realized that didn't help the prices, it just made another variant within the variant, for example


you can see there's multiple drop downs, because of the query i used to update the prices, in this case i updated twice. which means, that what you are seeing is not 3 seperate variants, but 1 variant with 3 extended prices.

How can I fix this with SQL ? It's only a few dozen products I've used this query on nothing major but I want a simple fix and a better update to the extended pricing due to the fact that I will be needing to update my account prices.

A simple way to do it would be to delete the rows for the variant first, and then re-insert the one you want, like this;

Code:

="DELETE FROM dbo.ExtendedPrice WHERE VariantID="&B2&"; INSERT INTO dbo.ExtendedPrice (VariantID, CustomerLevelID, Price) values ("&B2&","&D2&","&C2&");"

Thank you so much ESE, I swear you've helped me with almost my entire sql life with my site as well as you BFG- much appreciate from you both