 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Programming / SQL / July 2008Cannot delete table rows ?!?
In management studio - get weird message: error source: Microsoft.Visual Studio.DataTools error message: 'row values updated or deleted do not make the row unique or alter multiple rows (5 rows)' This table has no primary key. Why doesn't the table have a primary key? How else do you expect SSMS to identify which row you are talking about, if it can't tell two rows apart because all of the column values are the same? You should not be performing data manipulation within Open Table, anyway. You should use a DELETE statement in a query window. To successfully perform the delete, change the query to a SELECT and fiddle with the where clause until you find the single row that you want. On 7/30/08 4:46 PM, in article 775ECDB7-3FFA-47F3-97F4-5B4C7844A2F5@microsoft.com, "mscertified" <rupert@tigerlily.com> wrote: > In management studio - get weird message: > [quoted text clipped - 3 lines] > > This table has no primary key. I'm attempting to delete via Management Studio. This is test data. I click the row I want to delete but it still cant do it. > Why doesn't the table have a primary key? How else do you expect SSMS to > identify which row you are talking about, if it can't tell two rows apart [quoted text clipped - 15 lines] > > > > This table has no primary key. > I'm attempting to delete via Management Studio. This is test data. I click > the row I want to delete but it still cant do it. BTW, what are you certified in? Hi This happens when management studio can't identify the record that you want to delete. For e.g CREATE TABLE [dbo].[Table_1]( [id] [int] NULL, [cc] [char](10) NULL ) Data id cc ----------- ---------- 1 aa 1 aa 1 aa let say you want to delete the 2nd record from the table, by right clicking the 2nd row and selecting delete from the drop down menu. since all the data are same, management studio will not be able to locate the 2nd record. In this situation what I would do is, add an extra identity column, delete the record and remove the identity column vinu  Signaturevinu http://oneplace4sql.blogspot.com/ > In management studio - get weird message: > [quoted text clipped - 4 lines] > > This table has no primary key. > 2nd record. In this situation what I would do is, add an extra identity > column, delete the record and remove the identity column Instead of writing a DELETE statement in a query window? I don't think this is a very good answer at all. What if the table is huge? Aron, Thanks for correcting me.. could you please explain me how to delete the 2nd(only the 2nd,) record from follwoing example using DELETE statement CREATE TABLE [dbo].[Table_1] ( [id] [int] NULL, [cc] [char](10) NULL ) Data id cc ----------- ---------- 1 aa 1 aa --- i want to remove this record 1 aa 2 bb 2 bb also please explain me how to remove the duplicate records from the table but i want to keep one record from each group, i.e after the delete the table should contain only id cc ----------- ---------- 1 aa 2 bb Also i am using sql 2000 vinu http://oneplace4sql.blogspot.com/ >> 2nd record. In this situation what I would do is, add an extra identity >> column, delete the record and remove the identity column > > Instead of writing a DELETE statement in a query window? I don't think > this is a very good answer at all. What if the table is huge? > 1 aa > 1 aa --- i want to remove this record > 1 aa If you delete any of those three rows, how do you know which one was deleted? What exactly makes you want to delete the second one, instead of the third or the fourth? This is part of the problem with allowing duplicate data in the first place. Anyway, the answer is simple: SET ROWCOUNT 1; DELETE table WHERE id = 1 AND cc = 'aa'; SET ROWCOUNT 0; SELECT * FROM table; > also please explain me how to remove the duplicate records from the table > but i want to keep one record from each group, i.e after the delete the [quoted text clipped - 6 lines] > > Also i am using sql 2000 Well, the easiest way for me would be to: SELECT DISTINCT id, cc INTO dbo.newtable FROM dbo.oldtable; DROP TABLE dbo.oldtable; EXEC sp_rename 'dbo.newtable', 'oldtable', 'OBJECT'; Then add a PRIMARY KEY on (id, cc) if you want to not have to do this every day (or more)! Aron > Well, the easiest way for me would be to: > > SELECT DISTINCT id, cc INTO dbo.newtable FROM dbo.oldtable; > DROP TABLE dbo.oldtable; > EXEC sp_rename 'dbo.newtable', 'oldtable', 'OBJECT'; What if dbo.oldtable has a trigger or a check constraints on it..???? Accoring to your solution you will loose the trigger/check constraints right..??? > Then add a PRIMARY KEY on (id, cc) if you want to not have to do this > every > day (or more)! As you suggested add a PRIMARY KEY , what if the is very huge thanks vinu http://oneplace4sql.blogspot.com/ >> 1 aa >> 1 aa --- i want to remove this record [quoted text clipped - 30 lines] > every > day (or more)! > What if dbo.oldtable has a trigger or a check constraints on it..???? > Accoring to your solution you will loose the trigger/check constraints > right..??? Well yes, there are plenty of other things you will need to consider; indexes, triggers; constraints; partitioning; indexed views; etc. Since I wasn't told about other metadata, just "here is a simple two-column table where we allow duplicates for some reason", that is the problem I solved. >> Then add a PRIMARY KEY on (id, cc) if you want to not have to do this >> every >> day (or more)! > > As you suggested add a PRIMARY KEY , what if the is very huge Okay, then add an identity column if you really think that is the better answer. That will make your data unique! <shrug> ok, I added an unneeded column to make each row unique and that solved the problem. I still think its odd that Management Studio cannot delete a row when I have told it which row to delete. > Hi > [quoted text clipped - 34 lines] > > > > This table has no primary key. > ok, I added an unneeded column to make each row unique and that solved the > problem. This hasn't solved the problem at all. Again, this is like . Shouldn't you be concerned about preventing duplicate data get into the table in the first place? > I still think its odd that Management Studio cannot delete a row > when I have told it which row to delete. I think you are missing the concept that if you show Management Studio this... 1 aa 1 aa ...it does not know which one is which. How would you identify only one of those rows in a WHERE clause? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.