June 27, 2008 at 4:50 am
This should be fairly simple, just can't think of the way to do it more efficiently...
Say I've got a table with two columns and three rows of data. The first column is called nme, the second type.
nme | type
bob | 0
charlie | 1
fred | 1
If I wanted to delete all of the rows where type was 0, then I could issue a simple delete command.
However if I wanted to only delete all the rows where type was 0 if in the table I had at least one row with type 1, I could do this:
IF EXISTS (SELECT FROM tblName WHERE Type = 0) AND EXISTS (SELECT FROM tblName WHERE Type = 1)
BEGIN
DELETE FROM tblName WHERE Type = 0
END
But is there a better way?
Thanks in advance for you time.
Hope it makes sense.
June 27, 2008 at 5:01 am
Alternatively, avoiding the IF construct....
DELETE FROM tblname WHERE type = 0 AND EXISTS (SELECT nme FROM tblName WHERE Type = 1)
How do you mean 'better'?
Kev
June 27, 2008 at 5:08 am
As in rather than having to do my two hits to then do the delete, just in one statement with less code...
So for me yours looks better.
Thank you!
June 27, 2008 at 5:17 am
Ahh...
I was thinking much deeper!!!
Glad I culd help,
Kev
June 27, 2008 at 3:29 pm
Hi
This is anotherway to do the deletes.
DELETE FROM tblname t1 WHERE t1.type = 0 AND EXISTS (SELECT 1 FROM tblName t2 WHERE t2.Type = 1 and t2.nme = t1.name)
Thanks -- Vj
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply