Turning a SELECT statement into a DELETE statement

Results 1 to 2 of 2

Thread: Turning a SELECT statement into a DELETE statement

  1. #1
    Join Date
    Dec 1969
    Posts
    96

    Default Turning a SELECT statement into a DELETE statement

    I wish to eliminate the duplicate rows in my table. I created a SELECT statement which will find the duplicate rows. But when I try to turn it into the DELETE statement, it deletes all the rows, instead of leaving one row and only deleting the duplicate one. What am I doing wrong? Thanks for your help.<BR><BR>SELECT a.object_id, a.icon_file<BR>FROM fam_xtra_base AS a, fam_xtra_base AS b<BR>WHERE a.object_id =b.object_id<BR>AND a.icon_file = b.icon_file<BR>GROUP BY a.object_id, a.icon_file<BR>HAVING Count(a.object_id) &#062;1 and Count(a.icon_file)&#062;1<BR>ORDER BY a.object_id, a.icon_file;

  2. #2
    Nathen Grass Guest

    Default RE: Turning a SELECT statement into a DELETE state

    It seems kind of weird that you have duplicate rows in a table, unless you haven&#039t specified a primary key to enforce integrity. I don&#039t know if you&#039re using SQL Server but here is a method that may work. Create a Stored Procedure with a SELECT DISTINCT statement that gets each unique row and inserts them into a tempory table. Delete all rows from your table and insert the rows from your temp table back into the table you just deleted all the rows from. Your table should now have all unique rows.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •