By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,564 Members | 1,070 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,564 IT Pros & Developers. It's quick & easy.

delete particular rows in a table

P: n/a
hi all
i hava eetable that is ,
eename sal
_______ ________
suresh 100000
ramsesh 100000
raja 100000
susjssj 100000
dkddkd 100000
jfdjfdjfd 100000

so i want to delete from second to Fourth row by using Rownumber
..Rownumber is not a column of eetable give some example

Jul 25 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
If your table does not have Rownumber, then neither does SQL Server.
Relational tables do not have order, there is no first row, or fourth
row, no next and no prior.

If you can not write a SELECT that returns just the rows you want
deleted, they you will not be able to write a DELETE that will remove
that set of rows.

Roy Harvey
Beacon Falls, CT

On 25 Jul 2006 05:00:49 -0700, "surya" <su*******@gmail.comwrote:
>hi all
i hava eetable that is ,
eename sal
_______ ________
suresh 100000
ramsesh 100000
raja 100000
susjssj 100000
dkddkd 100000
jfdjfdjfd 100000

so i want to delete from second to Fourth row by using Rownumber
.Rownumber is not a column of eetable give some example
Jul 25 '06 #2

P: n/a

"surya" <su*******@gmail.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...
hi all
i hava eetable that is ,
eename sal
_______ ________
suresh 100000
ramsesh 100000
raja 100000
susjssj 100000
dkddkd 100000
jfdjfdjfd 100000

so i want to delete from second to Fourth row by using Rownumber
.Rownumber is not a column of eetable give some example
There is no such thing as a 4th row in a table. A table is by definition an
unordered set.

You need to specify each row manually if that's what your table looks like.
Jul 26 '06 #3

P: n/a
i hava eetable that is ,
eename sal
_______ ________
suresh 100000
-- SNIP --
so i want to delete from second to Fourth row by using Rownumber
-- SNIP --
Hi!

I've written up an example, but with different names. I used:
-- dbtwo as the database name (I don't know what your database name
is; you may not have to specify it, if you're using the default, so
just leave out the USE statement)
-- dbo.dummy1 as the table name (for you, this would be "eetable"
instead of "dummy1")
-- ename & sal as the column names (for you, this would be "eename"
instead of "ename")

To adapt it to your scenario, change the CURSOR FOR select statement
to:
DECLARE CUR1 CURSOR FOR SELECT eename, sal FROM eetable

Apart from that, the example achieves the objective of deleting rows 2
to 4. To improve performance, you could add a break if the @counter
exceeds 4, but I just wanted to keep it short and simple.
USE dbtwo
DECLARE CUR1 CURSOR FOR SELECT ename, sal FROM [dbo].[dummy1]
DECLARE @counter TINYINT
DECLARE @ename VARCHAR(50)
DECLARE @sal SMALLINT
OPEN CUR1
SET @counter = 1
FETCH NEXT FROM CUR1 INTO @ename, @sal
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @counter = @counter + 1
FETCH NEXT FROM CUR1 INTO @ename, @sal
IF ((@counter>1) AND (@counter<5))
BEGIN
DELETE FROM [dbo].[dummy1] WHERE ename=@ename AND sal=@sal
END
END
If you were using Oracle, you could've used ROWNUM, which is a
pseudocolumn for every result set... or you can write PL/SQL, just like
the above T-SQL example.

N.I.T.I.N.

Jul 26 '06 #4

P: n/a

"NiTiN" <em***********@gmail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...
i hava eetable that is ,

I've written up an example, but with different names. I used:
-- dbtwo as the database name (I don't know what your database name
is; you may not have to specify it, if you're using the default, so
just leave out the USE statement)
-- dbo.dummy1 as the table name (for you, this would be "eetable"
instead of "dummy1")
-- ename & sal as the column names (for you, this would be "eename"
instead of "ename")
Note there is absolutely NO guarantee this will work correctly!!!!

You are assuming that the rows will be returned in a particular order which
w/o a ORDER BY statement is not guaranteed.

Which means that your example will delete rows 2,3,4 of the dataset, but you
can't guarantee what rows those will be.

>
To adapt it to your scenario, change the CURSOR FOR select statement
to:
DECLARE CUR1 CURSOR FOR SELECT eename, sal FROM eetable

Apart from that, the example achieves the objective of deleting rows 2
to 4. To improve performance, you could add a break if the @counter
exceeds 4, but I just wanted to keep it short and simple.
USE dbtwo
DECLARE CUR1 CURSOR FOR SELECT ename, sal FROM [dbo].[dummy1]
DECLARE @counter TINYINT
DECLARE @ename VARCHAR(50)
DECLARE @sal SMALLINT
OPEN CUR1
SET @counter = 1
FETCH NEXT FROM CUR1 INTO @ename, @sal
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @counter = @counter + 1
FETCH NEXT FROM CUR1 INTO @ename, @sal
IF ((@counter>1) AND (@counter<5))
BEGIN
DELETE FROM [dbo].[dummy1] WHERE ename=@ename AND sal=@sal
END
END
If you were using Oracle, you could've used ROWNUM, which is a
pseudocolumn for every result set... or you can write PL/SQL, just like
the above T-SQL example.

N.I.T.I.N.

Jul 26 '06 #5

P: n/a
>want to delete from second to Fourth row by using Rownumber. Rownumber is not a column of eetable give some example <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. If you want an ordering, then you need to have a column
that defines that ordering. You must use an ORDER BY clause on a
cursor or in an OVER() clause.

You have missed the whole point of RDBMS!!

Jul 26 '06 #6

P: n/a
Somehow I knew you were going to say that.
On 26 Jul 2006 08:20:27 -0700, "--CELKO--" <jc*******@earthlink.net>
wrote:
>>want to delete from second to Fourth row by using Rownumber. Rownumber is not a column of eetable give some example <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. If you want an ordering, then you need to have a column
that defines that ordering. You must use an ORDER BY clause on a
cursor or in an OVER() clause.

You have missed the whole point of RDBMS!!
Jul 26 '06 #7

P: n/a
You are assuming that the rows will be returned in a particular order which
w/o a ORDER BY statement is not guaranteed.
True, but unless there are updations and insertions occuring, the order
of the rows is usually the same as that provided by a SELECT statement
(perhaps a statistics update would cause a new execution plan to
provide rows in a different order).

When you've got duplicate entries and you don't care which ones get
deleted, you could use a modified version of the example to delete all
but one.

N.I.T.I.N.

Jul 27 '06 #8

P: n/a

"NiTiN" <em***********@gmail.comwrote in message
news:11**********************@m79g2000cwm.googlegr oups.com...
You are assuming that the rows will be returned in a particular order
which
w/o a ORDER BY statement is not guaranteed.

True, but unless there are updations and insertions occuring, the order
of the rows is usually the same as that provided by a SELECT statement
(perhaps a statistics update would cause a new execution plan to
provide rows in a different order).
USUALLY but by no means always.

The posted code is absolutely unsafe.

>
When you've got duplicate entries and you don't care which ones get
deleted, you could use a modified version of the example to delete all
but one.
You could if you want to risk losing good data.

There are far better ways of doing this.

>
N.I.T.I.N.

Jul 28 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.