How to update Row which is not having Primary Key?

We all find some different scenario where the table has no primary key column and need to update the records having same data.

For this type of scenario's we need to update the particular column based on some Unique value.In SQL Server we have a option called "ROW_NUMBER()" .Every one know about Row_Number() I will present a brief intro about this.

ROW_NUMBER(): 

ROW_NUMBER to find the Row number of a particular column if its specified in orderby Clause.It has the following syntax 

Syntax:

select  row_number() over (order by name) as ROWNumber, Name from sys.all_objects order by name 

For more details about the ROW_NUMBER() refer this MSDN.

 
Here I will present a example query to update a table which has no primary key and having same data for few rows.

WITH Record AS (select  row_number() over (order by [columnName]) as ROWNumber, Name from sys.all_objects order by name )
 UPDATE Record set [col1]=@col1,[col2]=@col2,[col3]=@col3 where Record.rownum=@rownum


 columnName --> Column Name in which column you want to order by.

Pass the column values

1. @col1 --> column1 value  e.t.c


In C#.Net  we need to frame the query as above and pass the parameters.

Any quesry feel free to ask :_)
Hope this will help you :-)

Popular posts from this blog

Exporting to excel from a custom class object using C#.NET

Get the Clicked Cell Value and Column Name of GridView in ASP.NET

How to apply watermark to the textbox and dropdownlist?