how to manipulate number segments

Question

how to manipulate number segments

yankangch

Valued Member

Points: 65
More actions
May 21, 2010 at 8:36 pm

#221433

/*
the question is how to delete 30-140 from tb1,
get the result:
nbe
x1020
x3160
x141200
from
nbe
11020
22460
365110
4120130
5150200
*/
if object_id('[tb1]') is not null drop table [tb1];
create table [tb1]([n] int, int,[e] int);
create clustered index ix_b on tb1(b);
insert [tb1]
select 1,10,20 union all
select 2,24,60 union all
select 3,65,110 union all
select 4,120,130 union all
select 5,150,200;
select n ,b,e from tb1;

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

excellent job on giving us all the stuff we need to help with!

in this case, i thinkyou want to use the BETWEEN command in your where statement, that lets you select a range of values to compare the column set against, where the begin and end values are included:

if object_id('[tb1]') is not null drop table [tb1];

create table [tb1]([n] int, int,[e] int);

create clustered index ix_b on tb1(b);

insert [tb1]

select 1,10,20 union all

select 2,24,60 union all

select 3,65,110 union all

select 4,120,130 union all

select 5,150,200;

select n ,b,e from tb1;

--find the values that exist in the desired range

select n ,b,e from tb1 WHERE e BETWEEN 30 AND 140;

--delete the values that exist in the desired range

DELETE from tb1 WHERE e BETWEEN 30 AND 140;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

yankangch Valued Member Points: 65 More actions · Answer 2

thx,Lowell ,but that's not what i want.

in the table ,column b and e stand for begin and end of a member segment , and what i want is deleting the member segemnt 30-140, not deleting the whole rows.

i.e. for a row like 30-200 (b-e), afer the deletion should be a update

to set b=141