I am trying to delete some categories and their corresponding links from a Links SQL database between a certain range of FatherID's but I can't seem to get the syntax right could someone help please.....

The FatherID's are 5806 to 13487. Is there an easier way to do this?

I've tried some things but can only get it do delete certain categories and not their corresponding links....

DELETE * FROM Category WHERE FatherID="number";

I can't seem to get it to delete a range of categories and the corresponding data.

Thanks.

Oh I've also tried this but it just brings up the whole category and not the specific data......

SELECT * FROM Category WHERE (FatherID>"5805" & FatherID<"13488");

Maybe I just don't know much about MySQL queries




Paul
Installations:http://wiredon.net/gt/
Support: http://wiredon.net/forum/

Change that & to && ought to help

Jason Rhinelander
Gossamer Threads
jason@gossamer-threads.com

Doh!

Thanks :)

So would this do what Im after?....

DELETE * FROM Category WHERE (FatherID>"5805" && FatherID<"13488");

That won't delete the links within those categories also will it?



Paul
Installations:http://wiredon.net/gt/
Support: http://wiredon.net/forum/

Please could you tell me why this produces a MySQL error.......

DELETE * FROM Category WHERE (FatherID>"5805" && FatherID<"13488");

....but this doesn't......

SELECT * FROM Category WHERE (FatherID>"5805" && FatherID<"13488");

MySQL said: You have an error in your SQL syntax near '* FROM Category WHERE (FatherID>"5805" && FatherID<"13488")' at line 1.

Query: DELETE * FROM Category WHERE (FatherID>"5805" && FatherID<"13488")

EDIT:
Nevermind, I needed to use.......
DELETE FROM Category WHERE (FatherID>"5805" && FatherID<"13488");

Thanks :)


Paul
Installations:http://wiredon.net/gt/
Support: http://wiredon.net/forum/

Paul,

You do not need to use the wildcard character within a DELETE query.

You simply need to use the following:



Regards,

Eliot Lee

Thanks, I figured it out just after I posted, so I added the EDIT: bit.

That deletes the categories fine but how can I delete the links associated with those categories also?....I assume I need to use the CatLinks table but how do I know which ID's to delete?

Thanks.

(I hate sounding stuuupid )

Paul
Installations:http://wiredon.net/gt/
Support: http://wiredon.net/forum/

I would've gone the other direction...

1) First delete the links out of the intersection table (CatLinks).

2) Then delete the Categories in the Category table.

3) Then if you wanted to also delete links within the Links table, that could be done before deleting the Categories out of the Category table.

Always....delete records from Intersection tables before deleting records in the table where the foreign key is the primary key...

Regards,

Eliot Lee

Does the ID in the Category table match the ID in the CatLinks table?....

OK thanks so what you are saying is......

1) Delete from CatLinks
2) Delete from Links
3) Delete from Category

.....I'll give it a go thanks!

(I will have to re-import the dump I made :) )

Paul
Installations:http://wiredon.net/gt/
Support: http://wiredon.net/forum/

Uh ohhhhhhhhh

I dumped the whole database to a file but when I re-import it only re-creates three of the tables and half the data........arghhhhhhh.

Paul
Installations:http://wiredon.net/gt/
Support: http://wiredon.net/forum/

What I would do is the following:

1) Restore your tables from backup.
2) Then do a SQL DUMP to preserve the current table/column structure.
3) Then attempt the deletion suggestions I gave.

Regards,

Eliot Lee

mmmmmmmm this is really bugging me now.

I saw somewhere that Pugdog said if you delete a top level category from CatLinks then it will delete all sub-categories.....is that correct?

....because I just tried it and for some reason it said "4 rows affected".

DELETE FROM CatLinks WHERE CategoryID='5806';

It should say "7000 rows affected" or something like that.

Paul
Installations:http://wiredon.net/gt/
Support: http://wiredon.net/forum/

umm.. no

i don't remember the code for this.. i don't even use links sql 3.0 or whatever it is now so i have no clue what the tables are anymore.. but you have to have the categoryIDs.. that will only delete the CatLinks with that categoryID.. not the subs.. the line says that almost... its almost plain english..

delete from catlinks where categoryid = 5806

Jerry Su
http://www.jsu07.com

I know it doesn't work because I tried it :(

I am therefore trying to find a solution....maybe someone can give me a hint?

Paul
Installations:http://wiredon.net/gt/
Support: http://wiredon.net/forum/