Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2003
    Location
    Kalamazoo
    Posts
    42

    Unanswered: Deleting Records in a Table

    Hi,
    I have a table which has some columns which have repititive values. I want to keep the first value(record) of column(which has the repitive values) and then delete the other records which repeat. Please let me know.
    Thanks.
    Example:
    Table:
    column 1 Column2 Column3 Column4
    1 10 23 15
    2 12 26 14
    3 13 25 14
    4 100 250 14
    I want to delete records number 3-4 but retain the 2nd record.

  2. #2
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63
    Hello!

    Is column1 an ID-column in this table? If yes, you can identify the record to delete with the following query

    select * from table_b t1
    where t1.column1 not in (select min(t2.column1)
    from dbo.table_b t2
    where t2.column4 = t1.column4)

    If no, you should look up this post http://www.dbforums.com/t926686.html.

    If there are more quetions, post again!

    Greetings,
    Carsten

  3. #3
    Join Date
    Sep 2003
    Location
    Kalamazoo
    Posts
    42
    Originally posted by CarstenK
    Hello!

    Is column1 an ID-column in this table? If yes, you can identify the record to delete with the following query

    select * from table_b t1
    where t1.column1 not in (select min(t2.column1)
    from dbo.table_b t2
    where t2.column4 = t1.column4)

    If no, you should look up this post http://www.dbforums.com/t926686.html.

    If there are more quetions, post again!

    Greetings,
    Carsten
    Hi Carsten.
    Thanks for the SQL, but I am a little bit confused about the Query! I have just one table, but in your query you have stated Table_t1 and Table_t2...Please let me know.
    Thanks.

  4. #4
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63
    Hi,

    the only table used in this query should be "table_b". But this one twice! If you have a query accessing the same table more than once (like here, in the query and sub query) you should use the synonyms to ensure which table you exactly mean. The use of synonyms is like this:

    <synonym>.<column name>

    So, just one table is used, but in two different forms.

    Greetings,
    Carsten

  5. #5
    Join Date
    Sep 2003
    Location
    Kalamazoo
    Posts
    42
    Originally posted by CarstenK
    Hi,

    the only table used in this query should be "table_b". But this one twice! If you have a query accessing the same table more than once (like here, in the query and sub query) you should use the synonyms to ensure which table you exactly mean. The use of synonyms is like this:

    <synonym>.<column name>

    So, just one table is used, but in two different forms.

    Greetings,
    Carsten
    Where and How do we Delete the repititive records from the Original Table???
    Thanks Again!

  6. #6
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63
    Now that you know (and see) which record to delete, you only need to exchange the "SELECT *"-statement for the "DELETE"-statement.

    Carsten

  7. #7
    Join Date
    Sep 2003
    Location
    Kalamazoo
    Posts
    42
    Originally posted by CarstenK
    Now that you know (and see) which record to delete, you only need to exchange the "SELECT *"-statement for the "DELETE"-statement.

    Carsten
    Thank you, Mr. Carsten!!!!!

  8. #8
    Join Date
    Sep 2003
    Location
    Kalamazoo
    Posts
    42
    Originally posted by CarstenK
    Now that you know (and see) which record to delete, you only need to exchange the "SELECT *"-statement for the "DELETE"-statement.

    Carsten
    Hi Carsten,
    I am using Sybase Central and as a result only the Query with Select statement in it is working but when I replace it with Delete....it is not working! Any suggestions for this??

  9. #9
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63
    Hi there,

    the normal syntax for delete looks like

    delete from <table_name>
    [where <where_condition>

    Carsten

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You probably left the * after the delete statement, which is MS Access syntax but is not acceptable in SQL Server.

    Here is my preferred method, using joins instead of where clause:

    delete
    from YourTable
    inner join
    (select column4, min(column1) column1
    from YourTable
    group by column4) FirstValues
    on YourYable.column4 = FirstValues.column4
    where YourTable.column1 > FirstValues.column1

    WHERE clauses with subquerys and NOT IN statements are not as efficient as table joins, although in some cases the optimizer can convert the syntax to a JOIN prior to developing an execution plan.

    blindman

  11. #11
    Join Date
    Oct 2003
    Posts
    2

    Re: Deleting Records in a Table

    Hi, I used the owner moon:

    create table moon.tempt
    (tid integer primary key,
    value integer)

    insert into moon.tempt values( 1, 15)
    insert into moon.tempt values( 2, 14)
    insert into moon.tempt values( 3, 14)
    insert into moon.tempt values( 4, 14)

    delete t1
    from moon.tempt t1
    where
    t1.tid <> (select min(t2.tid)
    from moon.tempt t2
    where t2.value = t1.value)

    finally:

    select * from moon.tempt

    tid value
    ----------- -----------
    1 15
    2 14

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Again, while the optimizer might be able to convert this into standard JOIN syntax, if it cannot then you are essentially asking SQL Server to run

    select min(t2.tid) from moon.tempt t2 where t2.value = t1.value

    ...once for every record in table tempt. Not as efficient as a JOIN clause which only executes the subquery once.

    Also, the "<>" operator is particularly ineffecient, because it is generally non-sargable and cannot take advantage of indexes. As a matter of fact, it is the least efficient of all the comparison operators.

    blindman
    Last edited by blindman; 10-06-03 at 02:04.

Posting Permissions

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