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());             List lstPropertiesNames = new List ();             foreach (PropertyInfo prop in props)             {                 lstPropertiesNames.Add(prop.Name);             }             return lstPropertiesNames;         } We need to call this method to create the worksheet header's as given beloe                 List lstProperties = GetPropet
Read more

Why Dispose() is preferred than Finalize() for Un-Managed Resources in .NET?

Image
Memory management is very important in any application development.The Garbage collector of .NET does almost all clean up activity for your objects.For unmanaged resources (Ex:Windows API created objects, File, Database connection objects, COM objects, etc.) are outside the scope of .NET framework. Why Dispose() is preferred than Finalize() If we use the " Finalize() " method,the  Garbage collector has to make two round in order to remove the objects. For an instance ,let me explain clearly if we have two objects as Object1 and Object2 and we finalize the Object2 as given in the below image.The GC has to categorize the two objects and put the Finalize objects into Finalization Queue.The GC will clean the object1 which doesn't use the Finalize() method and process the second round of cleaning the Finalization Queue. Now at this the " Dispose() " method will come into picture.This method belongs to IDisposable() interface .So the best practise to rele
Read more

How to apply watermark to the textbox and dropdownlist?

Create a watermark effect on your TextBox and display instructions to users, without taking up screen space.This warkmark style can applied by using the CSS and append this to your control using jquery. Hi this is basic where we you use to get to apply watermark to the Textbox or DropDown.I have implemented this by using the jquery. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="WaterMark.aspx.cs" Inherits="WaterMark" %> < html xmlns ="http://www.w3.org/1999/xhtml"> < head runat ="server">      < title > Water Mark using jquery title >      < style type ="text/css">         .watermarkOn         {             color: #CCCCCC;             font-style: italic;         }       style >      < script type ="text/javascript" src ="http://code.jquery.com/jquery-1.4.1.js"> script >      < script ty
Read more