Sql Server: Without Primart Key Column Update First or Last N Records In table

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.

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.

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

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

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

• Tweet
• Share
• Share
• Share
• Share

About Nikunj Satasiya

I am a passionate programmer and a Software Engineer love blogging and developing applications in Microsoft Technologies.

Related Post