2009/11/11 Jörgen Hägglund <[email protected]>: > 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) Logically the above should work, but it requires two sub-selects. Others will likely suggest a better way. > Regards, > /Jörgen > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu ----------------------------------------------------------------------- Assertions are politics; backing up assertions with evidence is science ======================================================================= Sent from Madison, Wisconsin, United States _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
