CODEWITHSUNDEEP
c#sql-server
Aakshay Subramaniam
Aakshay Subramaniam

Reputation: 130

Create an UPDATE statement without primary key

My code is as follows:

"UPDATE Mytable SET Phone= '"+PhoneNumber+"', 
 Age= '"+Age+"' 
 Where Name= '"+Name+"'";

Now the name here is not unique nor is the primary key. When the code searches for the match, it has to input the data in the latest record in descending order.

If the name list in the database table was:

  1. Adam
  2. Steve
  3. Paul
  4. Peter
  5. Homer
  6. Paul

The data has to fit in 'No.6-Paul' and not 'No.3-Paul'

Thanks a lot

Upvotes: 1

Views: 9665

Answers (2)

StuartLC
StuartLC

Reputation: 107237

To update through a known ordering, you could use a strategy to update through the same view or projection used to generate your original query, and provided that you are just updating data in one table:

WITH cteUpdate AS
(
   SELECT TOP 1 *
   FROM MyTable
   WHERE Name ='Paul'
   ORDER BY YourOrderColumnsHere DESC
)
UPDATE cteUpdate
   SET Phone= @PhoneNumber, Age= @Age;

Please remember to also parameterize your C# Code!

SqlFiddle here

However, note that this is vulnerable to concurrency issues, e.g. if another connection / user has already updated the record in the interim (e.g. since a button was clicked on screen), you might not be updating the Paul that you thought you were.

If you need a deterministic update, you don't have many options without a key. The best bet might be the optimistic concurrency hack, viz extending the WHERE to include ALL columns of the previously known value of the record, i.e. 

UPDATE ... 
WHERE Name = 'Paul' 
  and Phone='{oldValueOfPhone}' 
  and Age = '{oldValueOfAge}';

Upvotes: 5

dean
dean

Reputation: 10098

Something like this:

;with x as (
  select Phone, Age, row_number() over(partition by Name order by ID desc) as rn
  from MyTable
)
update x
set Phone= @PhoneNumber, Age=@Age
where Name= @Name and rn = 1;

Upvotes: 1

Related Questions