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 :-)

Comments

Post a Comment

Popular posts from this blog

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

We may face a scenario to export the data of custom class object to excel using InterOp .You can get the property name's from the class object using Reflection.Create a work sheet header by reading the each propety of the class object.I have created a separate method to get the all properties into a list collection of string as given below         ///         /// Get the list of Properties Name into a list collection.         ///         ///         ///         public List GetPropeties(Employee objEmployee)         {             Type myType = objEmployee.GetType();             IList props = new List (myType.GetProperties());  ...
Read more

How to hide bootStrap popover when user click(s) outside the popover?

Hiding bootStrap popover when user click(s) outside the popover Recently I have experienced this problem i.e., hiding the popover when we click outsode the popover.I have worked on it and fixed the issue.So,I thought to share this fix. For more detail(s) about the bootStrap please refer this link PopOver popover plugin has a property called " trigger " of type String with default event as " click " and popover can be triggered by click/hover/focus/manual.  To show the popover we need to prepare the HTML mark-up as given below < div class = " btn-group " > < div class = " btn-group " id = " ddlSelected " style = " clear: both; " > < button class = " btn btn-primary btnSelectedStyle selectedBorderStyle " type = " button " > Your selected Items ( < b id = " lblSelectedItemCount " > 0 < / b > ) < / butt...
Read more

How to Clear the Textbox Text using C#?

This is one of the common requirement we get in our projects.Text box has a wonderful method called " Clear ".This method is used to clear the text in the Text box.I have given a simple example to explain this .I have implemenetd a common method to clear the Text of few Controls .One of that is Textbox. void ClearInputs(ControlCollection ctrls) { foreach (Control ctrl in ctrls) { if (ctrl is TextBox) ((TextBox)ctrl).Text = string .Empty; ClearInputs(ctrl.Controls); } }   You can call this method where ever you want to clear the Text from the Textbox. For more information please click this link TextBox       Hope this would help the beginners. Cheers.
Read more