How to update record without primary key?

If we have any primary key in table then it is very easy to update the records  in the database table, but generally we face problem when we don't have primary in table because on that time we can not use any particular column to update records because that column can contain duplicate values.

But there is work around for this problem, we can use” ROW_NUMBER()” function to generate identity value for each record and then based on that “ROW_NUMBER” we can delete record from the table.

In this article, I am explaining - How to update nth record of the table?
Or
How to update record without primary key?


For this I am using “ROW_NUMBER()” function in query, Here is syntax -

Syntax-

WITH Record AS (SELECT *, ROW_NUMBER() OVER (ORDER BY [ColumnName]) AS rownum FROM TableName where condition) update Record  set  col1=value ,col2=value where Record.rownum = value

Example-

WITH Record AS (SELECT *, ROW_NUMBER() OVER (ORDER BY EmpName) AS rownum FROM tab_Employee ) update Record  set  Age=25 ,Salary=45000 where Record.rownum = 1


In this example,   “ROW_NUMBER()” is a function which will generate sequence number for each record, here ordering will be based on EmpName column, you can change it.

Here "rownum" will be additional column in the result set, this will be used to update the record.

Here “Record “ is result set.


Try this example.


Thanks
 

How to delete nth row of the table in SQL Server?

If database table contains any primary key value then performing update and delete operations are not big task. But generally we face problem when we don’t have any primary key in table, on that case we can’t target to any particular column for deleting or updating records because that column can contain duplicate values.

In this article, I am explaining –

How to delete record based on row number? 
Or
How to delete nth row of the table?           
Or
How to delete record without primary key?           
 
                             
To perform this operation we have to use ROW_NUMBER() function of SQL Server. This function will help us to get nth row which we want to delete from table.

ROW_NUMBER()- 

ROW_NUMBER() is function in SQL Server which return sequential number for each record in result set. This returned number can be used as an identity for each record which is present in result set. When we don’t have any primary key in table then it is very useful to identify the record in result set. Using this sequential number we can perform operation like delete and update even we don’t have primary key for table.

Here is syntax to delete nth row from the table-

Syntax-

DELETE  Record
FROM    (
        SELECT  rowno = ROW_NUMBER() OVER (ORDER BY (Column_Name))
        FROM    Table_ame
        Where Condition
        ) AS Record
WHERE   Record.rowno = RowIndex

Here RowIndex is an index of the row which you want to delete from table. Here inner query will return result set including sequential number for each record and this will be ordered based on column_name which will be passed in ORDER BY clause. Finally outer query will perform delete operation based on given condition . 

Example-

Suppose if we want to delete 5th row of the Employee table then query will be like this-

DELETE  Record
FROM    (
        SELECT  rowno = ROW_NUMBER() OVER (ORDER BY (EmpName))
        FROM    Employee
        ) AS Record
WHERE   Record.rowno = 5

In above query, First inner query will be executed, which will generate result set over Employee table including row number over EmpName. Finally outer query will perform delete operation over result of inner query with given condition-

WHERE   Record.rowno = 5

This condition will detect 5th row from inner result and delete record from the table. In this example I didn't put any condition for inner query but if we have requirement then we have put where condition also to get result set like I have written in given syntax.


Thanks

How to get nth record form table?

If we are filling result to any data-source then it is easy to get nth record from that data-source, by passing row index we can access nth record, but for this we have to perform two steps.

1. First we have to fill data source
2. Based on row index we have to get data from data-source.

Instead of these steps, we can do this directly in database side only, for this we have to use ROW_NUMBER () in query. This function include one sequence number for each record in result set. For this first we have to include ORDER BY clause in query because based on that only ROW_NUMBER() function include one sequential number for record. 

Syntax-

select *
from (select ROW_NUMBER()  over (order by columnname) as rownum from tablename) t1
where t1.rownum =index

Here index is nth row; we have to replace with any number.

Here is example-
 
Select *
from (select ROW_NUMBER()  over (order by columnname) as rownum from tablename) t1
where t1.rownum =2

This query will return 2nd row from table. Result set contains sequence number based on ORDER BY clause which has performed over given column. Sequence number can be different if we are passing different column name for ordering. We can also put where condition in inner query.


Thanks