Please disable your adblock and script blockers to view this page
 

Sql Server: Without Primary Key Column Update First or Last n Records in Table

Introduction: In this article I am going to explain how to update first or last 5 or 10 or any number of rows from SQL Server table when there is no primary key or unique identity column to filter the records. Or we can say updating specified number of records from top or bottom of the table without having any unique identity column in the table.  

In previous articles i explained Without primary key select / delete first or last n records from  table and Remove duplicate records from table using CTE and Multiple queries to get all dates between two dates and Temporary tables, their types and examples to use and Update existing column values with linear serial numbers without loop

Description: To update specified number of records in table we need a unique column for example primary key column through which we can filter the first or last n number of records using TOP and ORDER BY clause as I have mention in my previous article Select or delete first or last n records from table

But what if there is no primary key column in the table? In such case we have to generate a unique value to each row of table using inbuilt Sql Function ROW_NUMBER() and then we can filter the records using Top and order by clause and perform update operation on them as mention below. 
Here I have also used CTE (Common Table Expression) to filter and update the records. You can use any query as per your choice.

Implementation: Let’s create a table and delete n number of record s from first or last.

--Create a temporaty table using following script
 CREATE  TABLE #tbBooks
( 
    BookName   VARCHAR(100),
    Author     VARCHAR(100),
    Publisher  VARCHAR(100),
    BookPrice  DECIMAL(10,2)
)

--Add some dummy data into the table
GO
INSERT INTO #tbBooks VALUES
('Asp.Net','Ajay','Rozy Publication',1200),
('C#.Net','Sahil','Jai Publication',1000),
('VB.Net','Nancy','Rozy Publication',970),
('MVC','Sahil','Amar Publication',1480),
('JAVA','Supreet','Sam Publication',850),
('PHP','Parvesh','Maya Publication',800),
('ORACLE','Sunny','Amar Publication',1110),
('MYSQL','Shaheed','Sam Publication',400),
('jQuery','Amit','Maya Publication',950)


 --Check inserted data
SELECT * FROM #tbBooks

BookName
Author
Publisher
BookPrice
Asp.Net
Ajay
Rozy Publication
1200.00
C#.Net
Sahil
Jai Publication
1000.00
VB.Net
Nancy
Rozy Publication
970.00
MVC
Sahil
Amar Publication
1480.00
JAVA
Supreet
Sam Publication
850.00
PHP
Parvesh
Maya Publication
800.00
ORACLE
Sunny
Amar Publication
1110.00
MYSQL
Shaheed
Sam Publication
400.00
jQuery
Amit
Maya Publication
950.00


--Update first 3 records
UPDATE t SET t.BookPrice=1500.00 FROM
(
SELECT TOP 3 ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNum, * FROM #tbBooks ORDER BY RowNum ASC
)t

--OR Update first 3 records using CTE 
;WITH CTE
AS
(
SELECT TOP 3 ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNum, * FROM #tbBooks ORDER BY RowNum ASC
)
UPDATE CTE SET BookPrice=1500.00

 --Check updated records
SELECT * FROM #tbBooks

BookName
Author
Publisher
BookPrice
Asp.Net
Ajay
Rozy Publication
1500.00
C#.Net
Sahil
Jai Publication
1500.00
VB.Net
Nancy
Rozy Publication
1500.00
MVC
Sahil
Amar Publication
1480.00
JAVA
Supreet
Sam Publication
850.00
PHP
Parvesh
Maya Publication
800.00
ORACLE
Sunny
Amar Publication
1110.00
MYSQL
Shaheed
Sam Publication
400.00
jQuery
Amit
Maya Publication
950.00


--Update last 3 records
UPDATE t SET t.BookPrice=20000.00 FROM
(
SELECT TOP 3 ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNum, * FROM #tbBooks ORDER BY RowNum DESC
)t

--OR Update last 3 records using CTE 
;WITH CTE
AS
(
SELECT TOP 3 ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNum, * FROM #tbBooks ORDER BY RowNum DESC
)
UPDATE CTE SET BookPrice=2000.00

--Check updated records
SELECT * FROM #tbBooks

BookName
Author
Publisher
BookPrice
Asp.Net
Ajay
Rozy Publication
1500.00
C#.Net
Sahil
Jai Publication
1500.00
VB.Net
Nancy
Rozy Publication
1500.00
MVC
Sahil
Amar Publication
1480.00
JAVA
Supreet
Sam Publication
850.00
PHP
Parvesh
Maya Publication
800.00
ORACLE
Sunny
Amar Publication
20000.00
MYSQL
Shaheed
Sam Publication
20000.00
jQuery
Amit
Maya Publication
20000.00

 Now over to you:
A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates. 
Previous
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily.. ConversionConversion EmoticonEmoticon