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