On Wed, Nov 11, 2009 at 09:36:41AM -0600, P Kishor scratched on the wall: > 2009/11/11 Jörgen Hägglund <jorgenhaggl...@netscape.net>: > > Hi all! > > I have three tables, one containing IP addresses as integers, lets call > > it 'base'. > > A second table containing IP ranges and the country code the range > > belongs to. Lets call this 'IPtoCC' with the fields IPFrom and IPTo as > > integers and CC as text. > > Then a third, 'Exclude', containing country codes i want to be excluded > > with a single field CC as text. > > What I need is to delete all records in 'base' where base.IP falls into > > a range of IPtoCC.IPFrom to IPtoCC.IPTo and that IPtoCC.CC is in Exclude.CC. > > Is it possible to do this in a single DELETE? > > > > DELETE > FROM base > WHERE IP BETWEEN > (SELECT IPFrom FROM IPtoCC JOIN Exclude ON IPtoCC.CC = Exclude.CC) AND > (SELECT IPTo FROM IPtoCC JOIN Exclude ON IPtoCC.CC = Exclude.CC)
I think you need to add WHERE clauses to the sub-selects so you pick the proper upper and lower bound for that base value. Something like "...WHERE base.ip >= IPtoCC.IPFrom AND base.ip <= IPtoCC.IPTo". Otherwise each sub-select may return a whole column of values. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
