February 27, 2010 at 12:32 pm
Hi
I probably need to learn a new technique to achieve what I need to get to.
If you assume two tables, Table 1 with a single column with 100 rows with numbers ranging from 1 to 100. Table 2 has two columns, a start and end number, it has 2 rows, first row 10,20 second row 50,60.
I want a statement to delete the rows in the first table where the values are to be found between the ranges in the second, in this example it would delete 10 through to 20, and 50 through to 60.
I have traied and failed to use 'where exists' as the subquery returns more than one row.
Obviously the actual application of the technique I need is a lot more complicated, but I wanted to try and explain it in the simplest terms.
Any help gratefully received.
Matt
February 27, 2010 at 1:39 pm
Use the SELECT statment to be sure that you retrieve the required values, and when that proves to true, then change the SELECT into a UPDATE
CREATE TABLE #Table1(id INT)
INSERT INTO #Table1
DECLARE @C AS INT
SET @C = 1
WHILE @C < 101
BEGIN
INSERT INTO #Table1(id) VALUES(@C)
END
CREATE TABLE #Table2(Istart INT, Iend INT)
INSERT INTO #Table2 (Istart,Iend)
SELECT 10,20 UNION ALL
SELECT 50, 60
SELECT id FROM #Table1
JOIN #TABLE2 ON
#Table1.id >= #Table2.Istart AND #Table1.id <= #Table2.Iend
Results of the select:
10
11
12
13
14
15
16
17
18
19
20
50
51
52
53
54
55
56
57
58
59
60
February 28, 2010 at 2:34 pm
Ron,
I have put your advice to good use and solved the problem.
Thanks very much
Matt
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply