Cannot delete rows that contain same data

Using SQL Sever 2005 Management Studio I am trying to delete rows that are
duplicated in a table. There is no Primary Key. I get an error message to
the effect that the rows cannot be deleted because it would effect other
rows. If I attempt to change a field in the row I get the same error. In
this application there may be many duplicate rows that will need to deleted
at various times.
Thanks,
Bob HillerNo primary key is often an indication of a data model problem. Even if this
is simply a staging table used as part of an ELT process, you can add a
surrogate key to facilitate set-based processing and use GUI tools.
Do all columns of 'duplicate' rows contain the same values? In SQL 2005,
you can specify a TOP clause on a delete statement to delete only a
specified number of rows like the example below. Similarly, you can use SET
ROWCOUNT in earlier version but need to be careful to execute SET ROWCOUNT 0
afterward.
CREATE TABLE Table1 (Col1 int)
INSERT INTO Table1 VALUES(1)
INSERT INTO Table1 VALUES(1)
INSERT INTO Table1 VALUES(1)
SELECT * FROM Table1
DELETE TOP (2) FROM Table1 WHERE Col1 = 1
SELECT * FROM Table1
Hope this helps.
Dan Guzman
SQL Server MVP
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:uLNoND3SGHA.4752@.TK2MSFTNGP10.phx.gbl...
> Using SQL Sever 2005 Management Studio I am trying to delete rows that are
> duplicated in a table. There is no Primary Key. I get an error message to
> the effect that the rows cannot be deleted because it would effect other
> rows. If I attempt to change a field in the row I get the same error. In
> this application there may be many duplicate rows that will need to
> deleted at various times.
> Thanks,
> Bob Hiller
>|||Dan,
Yes, All of the columns contain the same values. Is there any way to delete
these rows by hitting the delete key or right click/delete?
Thanks,
Bob Hiller
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uHlyng3SGHA.5736@.TK2MSFTNGP10.phx.gbl...
> No primary key is often an indication of a data model problem. Even if
> this is simply a staging table used as part of an ELT process, you can add
> a surrogate key to facilitate set-based processing and use GUI tools.
> Do all columns of 'duplicate' rows contain the same values? In SQL 2005,
> you can specify a TOP clause on a delete statement to delete only a
> specified number of rows like the example below. Similarly, you can use
> SET ROWCOUNT in earlier version but need to be careful to execute SET
> ROWCOUNT 0 afterward.
> CREATE TABLE Table1 (Col1 int)
> INSERT INTO Table1 VALUES(1)
> INSERT INTO Table1 VALUES(1)
> INSERT INTO Table1 VALUES(1)
> SELECT * FROM Table1
> DELETE TOP (2) FROM Table1 WHERE Col1 = 1
> SELECT * FROM Table1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
> news:uLNoND3SGHA.4752@.TK2MSFTNGP10.phx.gbl...
>|||AFAIK, you can't delete rows using the GUI when all columns have the same
value. This is because the tools generate a DELETE statement behind the
scenes and the row(s) you want to delete is ambiguous when all columns have
the same value. If you must use a GUI, you'll need to add a column to
uniquely identify a row:
ALTER TABLE Table1
ADD TempID int IDENTITY(1, 1)
Hope this helps.
Dan Guzman
SQL Server MVP
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:u$RtNV4SGHA.5156@.TK2MSFTNGP10.phx.gbl...
> Dan,
> Yes, All of the columns contain the same values. Is there any way to
> delete these rows by hitting the delete key or right click/delete?
> Thanks,
> Bob Hiller
>|||Bob,
As Dan has already said, you will probably struggle to delete these rows
using a GUI tool (e.g. Enterprise Manager) because there is no key defined
for the table. You should probably first add a key to the table, then do
your DELETE operations, and then, if you really don't want a key any more
you could remove the key definition (and in doing so, re-introduce the
design flaw).
There are probably ways around this, if you're prepared to write your
own SQL DELETEs rather than use a GUI to perform the delete ops. But that
doesn't change the fact that there is a design problem which needs to be
fixed. A correct RDBMS (database) design will always see a field (or set of
fields) defined as a unique key, even on temporary tables. Perhaps the only
exception would be staging tables used to clean data, but those will,
nevertheless, need a key defined at some point during the migration process.
HTH
Robert
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:u$RtNV4SGHA.5156@.TK2MSFTNGP10.phx.gbl...
> Dan,
> Yes, All of the columns contain the same values. Is there any way to
> delete these rows by hitting the delete key or right click/delete?
> Thanks,
> Bob Hiller
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:uHlyng3SGHA.5736@.TK2MSFTNGP10.phx.gbl...
>|||The Table I am referring to is just being used for testing. I was using a
random number generator in my program that I developed to send the data to
the table. The random number generator at some point started duplicating
numbers. I am now going to implement the table in production mode and there
will of course be a key. It is very important because I am recording
automotive serial numbers which must be unique.
Thanks,
Bob Hiller
"Robert Ellis" <robe_2k5@.n0sp8m.hotmail.co.uk> wrote in message
news:%23wuSOn4SGHA.424@.TK2MSFTNGP12.phx.gbl...
> Bob,
> As Dan has already said, you will probably struggle to delete these
> rows using a GUI tool (e.g. Enterprise Manager) because there is no key
> defined for the table. You should probably first add a key to the table,
> then do your DELETE operations, and then, if you really don't want a key
> any more you could remove the key definition (and in doing so,
> re-introduce the design flaw).
> There are probably ways around this, if you're prepared to write your
> own SQL DELETEs rather than use a GUI to perform the delete ops. But that
> doesn't change the fact that there is a design problem which needs to be
> fixed. A correct RDBMS (database) design will always see a field (or set
> of fields) defined as a unique key, even on temporary tables. Perhaps the
> only exception would be staging tables used to clean data, but those will,
> nevertheless, need a key defined at some point during the migration
> process.
> HTH
> Robert
>
>
> "Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
> news:u$RtNV4SGHA.5156@.TK2MSFTNGP10.phx.gbl...
>|||Hey Bob,
Since this sounds like it's an issue of data cleansing for development
work, try something like the following:
SELECT DISTINCT *
INTO newTable
FROM oldTable
TRUNCATE TABLE oldTable
INSERT INTO oldTable
SELECT *
FROM newTable
DROP TABLE newTable
HTH,
Stu|||Bob,
Fair enough mate!
Just a small observation, for what it may be worth, (and yes, I'm being
somewhat pedantic here):
If you've got a scenario where you're first building a test system, and
then shifting to production (which is practically always the case with any
form of software development) then you might as well spend the extra time
developing your test DDL as fully as possible, because doing so will aid you
when it comes to evaluating the behaviour and performance of your client
software. The point here is, if a unqiue constraint had been enforced on
your test table, you're client program would never have been able to insert
those "duplicate" rows -- and so, you'd have been a step ahead...
Wishing you well with your project,
Regards,
Robert
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:uWN7VS5SGHA.736@.TK2MSFTNGP12.phx.gbl...
> The Table I am referring to is just being used for testing. I was using a
> random number generator in my program that I developed to send the data to
> the table. The random number generator at some point started duplicating
> numbers. I am now going to implement the table in production mode and
> there will of course be a key. It is very important because I am recording
> automotive serial numbers which must be unique.
> Thanks,
> Bob Hiller
>
> "Robert Ellis" <robe_2k5@.n0sp8m.hotmail.co.uk> wrote in message
> news:%23wuSOn4SGHA.424@.TK2MSFTNGP12.phx.gbl...
>