Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Location
    Athens,GR
    Posts
    16

    Unanswered: clear identical submissions

    i want to clear from my db the doubles and trebles submisions and keep a unique.my db looks like:

    a.a code lastname firstname phone
    ----------------------------------------------------
    1 101 smith john 23452345
    2 101 smith john 23452345
    3 123 black mary 57645756
    4 654 white peter 45634564

    i want to look like

    a.a code lastname firstname phone
    ----------------------------------------------------
    1 101 smith john 23452345
    3 123 black mary 57645756
    4 654 white peter 45634564

    thank you .angelo

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Oracle 9i,

    delete from table
    where NOT (rowid = (select min(rowid) from table group by KEY));

    KEY is defined as a candidate key that is not enforced.
    Last edited by r123456; 03-03-04 at 06:28.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Dec 2003
    Location
    Athens,GR
    Posts
    16
    dear sir
    thank you for your reply.

    message returned: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    The statement has been terminated.

    thank you in advance

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select distinct columns into #temp from tableA
    delete from tableA
    insert into tableA select * from #temp
    drop table #temp
    alter table tableA add primary key (columns)
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use something like:
    PHP Code:
    DELETE FROM table
       WHERE EXISTS 
    (SELECT *
          
    FROM table AS b
          WHERE  b
    .id table.id
             
    AND b.code table.code
             
    AND b.lastname table.lastname
             
    AND b.firstname table.firstname
             
    AND b.phone table.phone
    Note that I used the php tag to get a monospaced font, this is just standard SQL-92 syntax that should run on any SQL based product.

    -PatP

Posting Permissions

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