PerfectPhase "This is not war, this is pest control!" - Dalek to Cyberman

Oct 24, 2006 at 8:48 AM

OK, I find myself with a silly little problem. I have a table with no primary key in MSSQL-2000 and I have as a result ended up with two identical records in the table. I need to delete one of them via SQL, either one don't care which!

This is just out of curiosity now, I deleted both in the end. I was thinking something using a Top (1) or some such thing but can't make it work; everything I try takes both rows

Cheers

Stephen.
Matthew van Eerde AKA Maurits

Oct 24, 2006 at 8:58 AM

Joe Celko talks about this in his SQL for Smarties book. I don't believe there is a convincing solution to this problem.

There are a couple of less-than-convincing ways, though.

-- create the table
CREATE TABLE T
(
    Foo as int,
    Bar as int
)

-- insert some duplicate values
INSERT INTO T(Foo, Bar) VALUES(1,2)
INSERT INTO T(Foo, Bar) VALUES(1,2)
INSERT INTO T(Foo, Bar) VALUES(1,2)

-- insert some other values
INSERT INTO T(Foo, Bar) VALUES(3,4)

-- now the work begins... eliminate the duplicate values;
CREATE TABLE T2
(
    Foo as int,
    Bar as int
)

-- ONE WAY:
-- pull over all the rows of T, consolidating duplicates
INSERT INTO T2(Foo, Bar)
SELECT
    Foo, Bar
FROM
    T
GROUP BY
    Foo, Bar
-- now delete everything from T, repopulate from T2, and delete T2

-- A DIFFERENT WAY:
-- pull over only the duplicates
INSERT INTO T2(Foo, Bar)
SELECT
    Foo, Bar
FROM
    T
GROUP BY
    Foo, Bar
HAVING
    COUNT(*) > 1

-- now delete only those rows from T that are represented in T2
DELETE
FROM
    T
WHERE
    EXISTS (
       SELECT * FROM T2
       WHERE T.Foo = T2.Foo AND T.Bar = T2.Bar
    )

-- put back a single copy
INSERT INTO T(Foo, Bar)
SELECT Foo, Bar FROM T2

-- finally delete T2
W3bbo Work hard; increase production; prevent accidents, and be happy.

Oct 24, 2006 at 9:12 AM

Matthew van Eerde wrote:

I don't believe there is a convincing solution to this problem.

How does SQL Server Enterprise Manager or Access delete specific rows then?
Antitorgo

Oct 24, 2006 at 9:59 AM

This worked for me just now in SQL 2005 -- Not sure if it works in SQL 2000.

Leveraging Matthew's example from above:

-- create the table
CREATE TABLE T
(
Foo INT, -- Note: No AS keyword here...
Bar INT
)

-- insert some duplicate values
INSERT INTO T(Foo, Bar) VALUES(1,2)
INSERT INTO T(Foo, Bar) VALUES(1,2)
INSERT INTO T(Foo, Bar) VALUES(1,2)

-- insert some other values
INSERT INTO T(Foo, Bar) VALUES(3,4)

SET ROWCOUNT 1 -- This is the key here
DELETE FROM T WHERE Foo = 1 AND Bar = 2

SET ROWCOUNT 0 -- This resets back to the normal behavior
JohnAskew 9 girl in pink sweater

Oct 24, 2006 at 10:02 AM

No unique primary key? Why?
Antitorgo

Oct 24, 2006 at 10:08 AM

I forgot -- SQL 2005 extended TOP to DELETE so this would work too:

DELETE TOP(1) FROM T WHERE Foo = 1 AND Bar = 2
Matthew van Eerde AKA Maurits

Oct 24, 2006 at 1:46 PM

W3bbo wrote:

Matthew van Eerde wrote:I don't believe there is a convincing solution to this problem.

How does SQL Server Enterprise Manager or Access delete specific rows then?

Good question
W3bbo Work hard; increase production; prevent accidents, and be happy.

Oct 24, 2006 at 2:48 PM

Matthew van Eerde wrote:

W3bbo wrote:

Matthew van Eerde wrote:I don't believe there is a convincing solution to this problem.

How does SQL Server Enterprise Manager or Access delete specific rows then?

Good question

I just created a table on my db server, since it lacks an identity colum, Access doesn't have any "Insert record" stuff, but I was able to get some stuff in with Query Analyzer.

After I inserted some rows, Access doesn't let me delete stuff.

Enterprise Manager has the option to delete a specific row available, but gives this error message:

---------------------------
SQL Server Enterprise Manager
---------------------------
Key column information is insufficient or incorrect. Too many rows were affected by update.
---------------------------
OK Help
---------------------------

After executing this SQL (captured with the Profiler tool):

set implicit_transactions on
exec sp_executesql N'DELETE FROM "AMS2".."Foobar" WHERE "Foo"=@P1 AND "Bar"=@P2', N'@P1 varchar(10),@P2 varchar(10)', 'foo ', 'bar '
IF @@TRANCOUNT > 0 ROLLBACK TRAN

So I guess it's impossible then.

UPDATE: I didn't know about Transaction Rollbacks until now, I'm impressed.
PerfectPhase "This is not war, this is pest control!" - Dalek to Cyberman

Oct 25, 2006 at 2:27 AM

JohnAskew wrote:

No unique primary key? Why?

I used the data import wizard to bring a table from an old database that had no key. The table now has a PK but the data had to be cleaned to do that. But the question on how to do this in the most efficient way remained as an intellectual challenge

Thank you for all for your answers!
thepuffin

Oct 25, 2006 at 2:31 AM

The normal way to do it would be to copy the UNION of the table with itself (to remove duplicates) to a temp table, truncate the table, then move the data back.
JChung2006

Oct 25, 2006 at 7:09 PM

INSERT INTO NewTableWithPK SELECT DISTINCT <all fields> FROM OldTable would work too.

Thread Closed

This thread is kinda stale and has been closed but if you'd like to continue the conversation, please create a new thread in our Forums,
or Contact Us and let us know.

Delete a single row via SQL

Thread Closed