VP-ASP :: Shopping Cart Software

Shopping Cart Software Solutions for anywhere in the World

US/Canada(Toll Free): +1 888 587 2278
Europe/UK: +44 (020) 7193 9408
Australia/New Zealand: +61 3 9016 4497

VP-ASP Shopping Cart Customer Forum

Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 VPCart Forum
 Customization
 deleting products
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chughes
Starting Member

USA
29 Posts

Posted - April 24 2007 :  12:56:52  Show Profile  Visit chughes's Homepage  Reply with Quote
Anyone know of an easy way to delete products? I have about 300 products I need to remove. Any help would be great.

devshb
Senior Member

United Kingdom
1898 Posts

Posted - April 25 2007 :  02:38:00  Show Profile  Visit devshb's Homepage  Reply with Quote
you could use our freebie advanced sql execution tool, and then do a delete statement using that:

http://www.bigyellowzone.com/shopexd.asp?id=114

eg:
delete from products
where [put your product where clauses here]



then delete the orphan records:

delete from prodcategories
where not exists
(
select 1 from products
where catalogid=intcatalogid
)


Simon Barnaby
Developer
[email protected]
www.BigYellowZone.com
Web Design, Online Marketing and VPASP addons

Edited by - devshb on April 25 2007 02:38:56
Go to Top of Page

chughes
Starting Member

USA
29 Posts

Posted - April 25 2007 :  18:01:02  Show Profile  Visit chughes's Homepage  Reply with Quote
Will this work for MySQL?

Craig Hughes
http://www.avaccessoryhouse.com
Go to Top of Page

devshb
Senior Member

United Kingdom
1898 Posts

Posted - April 25 2007 :  23:49:40  Show Profile  Visit devshb's Homepage  Reply with Quote
the freebie addon will work for access/sqlserver/mysql

the first delete statement will also work for access/sqlserver/mysql

the second delete statement will work for access and sqlserver, and it should work for mysql although some versions of mysql are very restrictive in the kind of "exists" clauses that it allows. to see whether or not the second delete statement would work on your version, try doing:


delete from prodcategories
where exists
(
select 1 from products
where catalogid=intcatalogid
and catalogid=-1
)


(which should execute, but not actually delete anything)
if that doesn't give you an error then it'll work on your version of mysql

Simon Barnaby
Developer
[email protected]
www.BigYellowZone.com
Web Design, Online Marketing and VPASP addons
Go to Top of Page

rvaga
VP-ASP Super User

USA
254 Posts

Posted - April 26 2007 :  12:50:07  Show Profile  Reply with Quote
Hi Simon,

The advanced SQL add-on that you offer is great, but keep in mind that some of us (at least me!) are a bit terrified of trying something, and suddenly dealing with disasterous results.

I suggested a bulk delete some time ago, it's too bad that it was not designed into 6.5 For those of us that manage hundreds if not thousands of products, it makes obvious sense that if there is a way to bulk upload, there should be a way to bulk delete. Even a page with say, 50 text boxes where one could type in ccode would be much easier than it is now. Type in the ccode in the text boxes, hit "delete." Simple, and would save a ton of time.
Go to Top of Page

chughes
Starting Member

USA
29 Posts

Posted - April 26 2007 :  16:21:16  Show Profile  Visit chughes's Homepage  Reply with Quote
I would have to agree..


Craig Hughes
http://www.avaccessoryhouse.com
Go to Top of Page

support
Administrator

4266 Posts

Posted - April 26 2007 :  17:02:02  Show Profile  Visit support's Homepage  Reply with Quote
Hi there,

I may be missing something however why can't you use the bulk delete feare in VPASP?

You can in the admin filter your data and select the number of records to display up to 200.

Then just select the Select All check box and hit Delete.

Is this not what you are looking for?

Thanks
Cam

VP-ASP Support
Go to Top of Page

chughes
Starting Member

USA
29 Posts

Posted - April 26 2007 :  18:23:01  Show Profile  Visit chughes's Homepage  Reply with Quote
I would like to import a speadsheet of products that are identified by the ccode field and delete those.

Craig Hughes
http://www.avaccessoryhouse.com
Go to Top of Page

chughes
Starting Member

USA
29 Posts

Posted - April 26 2007 :  18:53:00  Show Profile  Visit chughes's Homepage  Reply with Quote
Actually I just botch an import of products and now I need to delete almost 7000 prodcts. I imported a daily product feed that has all my products I am approved for but I only sell 11000 or so on my site and thinking it would only update the quantity for the products that are already loaded but istead it added all those additional products and the only fields it imported were the ccode and cstock. So now I need to delete all products with a productid > X. I just need to figure out what X is. Should I just use the same statements as above? Anyone know the corect syntax to use for this? Man, I cant beliweve I botched that up like that.......

Craig Hughes
http://www.avaccessoryhouse.com
Go to Top of Page

support
Administrator

4266 Posts

Posted - April 26 2007 :  20:44:20  Show Profile  Visit support's Homepage  Reply with Quote
Simon is the best one to advise on his script but when you import you need to make sure you match the import to an existing ujique field in the database.

For example we recommend you use ccode as this is generally a unique product code.

Thanks
Cam

VP-ASP Support
Go to Top of Page

rvaga
VP-ASP Super User

USA
254 Posts

Posted - April 27 2007 :  01:07:00  Show Profile  Reply with Quote
Hi Cam,

For example, I receive about 50-100 of these per week:

"The following items are no longer in print:"
(these are my ccodes, I use catalog numbers as ccode)

490447
49043348
493349
49040
490451
490432
490353
443354
404355
49056
49043358

If I could cut/paste these onto ONE vpasp page and then click a delete button, it would be so much faster than doing them one at a time, waiting for the page to act, then doing it again for the next individual ccode, etc.

Or, perhaps entering them all as a string, separated by commas on a vpasp page? Or. . .
Go to Top of Page

dafragan
Starting Member

45 Posts

Posted - April 27 2007 :  01:12:46  Show Profile  Reply with Quote
Hi!

Couldn't you just use the advanced display of shopa_editdisplay.asp and type in your own query like this -

select * from products where catalogid in (490447,49043348,493349,49040,490451,490432,490353,443354,404355,49056,49043358)


Then they're all on the one screen and you could just delete them.

Seb
Go to Top of Page

chughes
Starting Member

USA
29 Posts

Posted - April 28 2007 :  01:26:56  Show Profile  Visit chughes's Homepage  Reply with Quote
I ended up useing the query from above that Simon suggested snd iti worked great. I was able to delete records where ccode was > than x. Thanks guys!

Craig Hughes
http://www.avaccessoryhouse.com
Go to Top of Page

rvaga
VP-ASP Super User

USA
254 Posts

Posted - April 29 2007 :  14:07:36  Show Profile  Reply with Quote
Thanks for good advice from everyone above. Looks like in vs. 6.09/SQL2005 I will be able to do a bulk delete via SQL query.

In vs. 5.5/SQL2000 though, there is not this advanced capability. In shopa_query.asp I can run the following query:

SQL=SELECT * from products Where (ccode like '%306040%' Or ccode like '%220019%') order by catalogid ASC

But, running the above simply shows the results, I can't delete.

Question is, can I run a query similar to the above in 5.5 that will delete the ccodes (products) in vs. 5.5/SQL2000?

Thought I'd try DELETE instead of SELECT as a roll of the dice, but figured it may do nothing, or screw up my database big time. Better to ask before pulling the trigger. . .

Simon, I do have your free Advanced SQL Execution shopa_doexecutesql.asp on my 5.5 site. Thought I'd mention this as it's perhaps a better means (or only means) of getting the bulk delete concept to work?

Edited by - rvaga on April 29 2007 14:11:02
Go to Top of Page

devshb
Senior Member

United Kingdom
1898 Posts

Posted - April 30 2007 :  00:03:29  Show Profile  Visit devshb's Homepage  Reply with Quote
yep; always a good idea to do a select to test your conditions before you run the corresponding delete.

the corresponding delete statement for:

SELECT * from products Where (ccode like '%306040%' Or ccode like '%220019%') order by catalogid ASC

would be:

delete from products
where (ccode like '%306040%' Or ccode like '%220019%')



then delete the orphan records (the orphan-record deletion would stay the same regardless of your first delete statement, as it always simply deletes any invalid/missing products from the matrix so in theory should never do any harm). Alternatively, if you've got our prod/cat check/fix tool (byz055) you'll be able to see/delete these straggling matrix records via that.

delete from prodcategories
where not exists
(
select 1 from products
where catalogid=intcatalogid
)


Simon Barnaby
Developer
[email protected]
www.BigYellowZone.com
Web Design, Online Marketing and VPASP addons
Go to Top of Page

standout
VP-ASP New User

Canada
70 Posts

Posted - January 06 2011 :  19:19:12  Show Profile  Visit standout's Homepage  Reply with Quote
Does 7.0 allow bulk delete via a spreadsheet as noted in the earlier posts?

STANDOUT Promotional Products
Port Perry, Ontario, Canada
www.standoutcanada.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
Snitz Forums 2000