Thread: Need help writing a Query

Hello everyone,

Just recently I have discovered an error on all of the files that are uploaded.

We have uploaded product options (kits) as nonpublished products. However, problem in those unpublished products is the fact that COST Price ended up as a sale price, and the cost is now empty.

I need a query that will check every row and move Sale price to cost price.


thanks

C#/VB.NET Code:

UPDATE Productvariant SET Cost = SalePrice;
UPDATE ProductVariant SET SalePrice = 0.00; 


Insert obligatory comment about how you should test this before using on a live server - make half a dozen backups, test the backups & pay someone a fortune to verify the query & guarantee it will work.



TTFN

BFG

Originally Posted by BFG 9000

C#/VB.NET Code:

UPDATE Productvariant SET Cost = SalePrice;
UPDATE ProductVariant SET SalePrice = 0.00; 


Insert obligatory comment about how you should test this before using on a live server - make half a dozen backups, test the backups & pay someone a fortune to verify the query & guarantee it will work.



TTFN

BFG

Thanks for the query. Now if I dare to ask, how would I add that this should be run only on products that are unpublished?

Thanks

This is what I have come up with:

Code:

declare @id int

declare cur CURSOR LOCAL for
select id = ProductID  from Product WHERE (Published = '0')

open cur

fetch next from cur into @id 

while @@FETCH_STATUS = 0 BEGIN

UPDATE Productvariant SET Cost = SalePrice WHERE VariantID = @id;
UPDATE ProductVariant SET SalePrice = 0.00 WHERE VariantID = @id; 

fetch next from cur into @id 
END

close cur
deallocate cur

C#/VB.NET Code:

UPDATE Productvariant SET Cost = SalePrice WHERE Published = 0;
UPDATE ProductVariant SET SalePrice = 0.00 WHERE Published = 0; 


TTFN

BFG

Originally Posted by BFG 9000

C#/VB.NET Code:

UPDATE Productvariant SET Cost = SalePrice WHERE Published = 0;
UPDATE ProductVariant SET SalePrice = 0.00 WHERE Published = 0; 


TTFN

BFG

Now I am confused totally... my code is bad?

What I noticed when I run this code on my test DB instance, it did chagned those values... but DB become slow as hell. I did the same thing three times in a row, and still it is slow as hell

Your "slow" problem is in using cursors. Absolutely no need in this case. Also, your query is wrong:

Code:

declare @id int

declare cur CURSOR LOCAL for
select id = ProductID  from Product WHERE (Published = '0')

open cur

fetch next from cur into @id 

while @@FETCH_STATUS = 0 BEGIN

UPDATE Productvariant SET Cost = SalePrice WHERE VariantID = @id;
UPDATE ProductVariant SET SalePrice = 0.00 WHERE VariantID = @id; 

fetch next from cur into @id 
END

close cur
deallocate cur

You are using Product.ProductID in the cursor to update the ProductVariant table based on the VariantID, so all of your records will get mixed up. You will need to revert to your backup before doing it again.

Code:

UPDATE pv SET Cost=SalePrice FROM ProductVariant pv JOIN Product p ON p.ProductID=pv.ProductID WHERE p.Published=0
UPDATE pv SET SalePrice=0 FROM ProductVariant pv JOIN Product p ON p.ProductID=pv.ProductID WHERE p.Published=0

Thank you

I will test this query ASAP

I lost my recently loaded files. i don't know what is the reason behind this?? please help me so that i get my recently loaded files.

Visit here.. http://www.prlog.org/12114868-lenswo...o-grab-it.html

What files Sarah?