Tech Off Thread

11 posts

Delete a single row via SQL

Back to Forum: Tech Off
  • User profile image
    PerfectPhase

    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 Smiley

    Cheers

    Stephen.

  • User profile image
    Matthew van Eerde

    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

  • User profile image
    W3bbo

    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?

  • User profile image
    Antitorgo

    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

  • User profile image
    JohnAskew

    No unique primary key?  Why?

  • User profile image
    Antitorgo

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

    DELETE TOP(1) FROM T WHERE Foo = 1 AND Bar = 2

  • User profile image
    Matthew van Eerde

    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 Smiley

  • User profile image
    W3bbo

    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 Smiley


    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.

  • User profile image
    PerfectPhase

    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 Smiley

    Thank you for all for your answers!

  • User profile image
    thepuffin

    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.

  • User profile image
    JChung2006

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

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.