deleting rows

[harcaype]

Is it possible in a query to delete a row specifying its row index? I want to delete a row from my table. I'm trying to figure this because when I delete rows from my datagridview, it does not update. So I was thinking if I just directly delete it from sql then it's just like calling a query.

[wujtehacjusz]

Your table must have a primary key... use this to delete that required row. The row number will change depending on which column you order your table.

Say that you have a table with id as primary key and date (you order your records according to date). You want to delete row number 5.

delete from exampleTable
where id=
(
[INDENT]select id from 
[INDENT](
select *, row_number() over (order by date) as row 
from exampleTable
) [/INDENT]
where row=5
[/INDENT])

There might be an easier way of doing this...

Going away from this solution... If you know what are the primary key values of record you want to delete just use that.

[harcaype]

thanks! but when i tried the "row" it was not recognized by sql. is this a variable which has been initialize or something? i had an error near the "WHERE row=5" statement

[wujtehacjusz]

Row is an alias for

row_number() over (order by someColumn)

To be precise:

(
select *, row_number() over (order by date) as row 
from exampleTable
)

Will give you every row from your example table with its row number according to column of your choice.
Then you just select the primary key of the row with desired row number and use it in where clause of the delete.

Does it make sense?

If you post the structure of your table I can rewrite this query for you.

[harcaype]

i see. i get it now. I got it working. Thanks a bunch! =)

[wujtehacjusz]

No prob...