Author |
Topic |
|
chughes
Starting Member
USA
29 Posts |
Posted - April 24 2007 : 12:56:52
|
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
1904 Posts |
Posted - April 25 2007 : 02:38:00
|
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 |
|
|
chughes
Starting Member
USA
29 Posts |
|
devshb
Senior Member
United Kingdom
1904 Posts |
Posted - April 25 2007 : 23:49:40
|
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 |
|
|
rvaga
VP-CART Super User
USA
254 Posts |
Posted - April 26 2007 : 12:50:07
|
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. |
|
|
chughes
Starting Member
USA
29 Posts |
|
support
Administrator
4679 Posts |
Posted - April 26 2007 : 17:02:02
|
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 |
|
|
chughes
Starting Member
USA
29 Posts |
Posted - April 26 2007 : 18:23:01
|
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 |
|
|
chughes
Starting Member
USA
29 Posts |
Posted - April 26 2007 : 18:53:00
|
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 |
|
|
support
Administrator
4679 Posts |
Posted - April 26 2007 : 20:44:20
|
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 |
|
|
rvaga
VP-CART Super User
USA
254 Posts |
Posted - April 27 2007 : 01:07:00
|
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. . .
|
|
|
dafragan
Starting Member
45 Posts |
Posted - April 27 2007 : 01:12:46
|
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 |
|
|
chughes
Starting Member
USA
29 Posts |
Posted - April 28 2007 : 01:26:56
|
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 |
|
|
rvaga
VP-CART Super User
USA
254 Posts |
Posted - April 29 2007 : 14:07:36
|
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 |
|
|
devshb
Senior Member
United Kingdom
1904 Posts |
Posted - April 30 2007 : 00:03:29
|
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 |
|
|
standout
VP-CART New User
Canada
70 Posts |
Posted - January 06 2011 : 19:19:12
|
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 |
|
|
|
Topic |
|