I have a Database DB with a table name population but with no Primary Key. Means it can have duplication of data. For example: I have 5 families (f1,f2,f3,f4,f5) with different members inside it (and members may have same name). So I can have exactly same type of record in more than 1 row. Now I want to edit just 1 member of the family, but it is editing all the duplicate records. What I want to do is, I just want to Update my Database once and only once. In other words, I want to use UPDATE command to execute just once. How to do it? I am using Sql Server Express, VS2010, C# 4.0 (if this info matters). I am pretty sure my problem may sound stupid to some people (probably many). But it is just a dummy of my problem. Any help or suggestion will be greatly appreciated. Thanks
-
2If the names are duplicate and you don't have a primary key, how would identify the "one" row you want updated?Joe Stefanelli– Joe Stefanelli2011-09-27 18:24:32 +00:00Commented Sep 27, 2011 at 18:24
-
hmmm, by checking different column names, and no matter how many rows it encountered to be matching, it has to update just one and exit. but to your query, one upvoteSandy– Sandy2011-09-27 18:26:43 +00:00Commented Sep 27, 2011 at 18:26
-
You still have to answer the question of which row to update.Joe Stefanelli– Joe Stefanelli2011-09-27 18:28:32 +00:00Commented Sep 27, 2011 at 18:28
-
it has to update the first matching column (based on where clause), and then exitSandy– Sandy2011-09-27 18:31:13 +00:00Commented Sep 27, 2011 at 18:31
-
"First" is not identified with a WHERE clause. It would require the use of an ORDER BY. So, how do you order the many encountered rows to determine which is "first?"Joe Stefanelli– Joe Stefanelli2011-09-27 18:34:17 +00:00Commented Sep 27, 2011 at 18:34
4 Answers
I know it's not exactly what you're asking but seriously, the easiest option is to alter the database to have a primary key and use that. Perhaps an Identity key....
Without that, you could update just one record, but you have no guarantee of which record. This is why primary keys are such as fundamental concept. I suppose this doesn't really matter if they are all the same, so....
If you really want to proceed without a primary key, you need to use the TOP keyword as shown here: How do I update n rows in a table?
Set it to
UPDATE TOP 1 ....
Comments
Add an identity column, ID int with auto increment on. Then update using the id's.
CREATE TABLE dbo.Family
(
Id int NOT NULL IDENTITY (1, 1),
FamilyName varchar(50) NULL
)
update dbo.Family set FamilyName = 'xxx' where Id = y
1 Comment
In case you can't add an identity column for some reason:
UPDATE TOP ( 1 ) Families
SET Whatever = 'Your Value', ...
WHERE <Your where clause>
6 Comments
The real answer is: Fix your database design. Every record should have some unique identifier. Add an auto-increment field or something.
To directly answer your question, you can say "update top (1) ..." to only update one record. But without some unique identifier, how do you know which record to update? The program will essentially update a random record. Which takes me back to point 1.
Edit: Whoops, my original answer was for a different engine. Corrected above.
1 Comment