Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132

    Post Unanswered: dealing with double records

    I have a table on which I forgot to put an index key combination.
    This is a relation table like that:
    Code:
    relation_id | user_id | product_id
    I'd like to remove doubloons between user_id and product_id.
    A user can't be associated multiple times with the same product.

    Is it possible to remove them with a query ?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    longterm, your strategy should be to remove relation_id and make the other two a composite primary key

    for now, you will want to run this --
    Code:
    create table keepers
    select min(relation_id) as min_rel
         , user_id 
         , product_id
      from yourtable
    group
        by user_id 
         , product_id
    having count(*) > 1     
    ;     
    delete yourtable
      from yourtable
         , keepers
     where yourtable.user_id = keepers.user_id
       and yourtable.product_id = keepers.product_id
       and yourtable.relation_id > keepers.min_rel
    ;
    as always, before you run any sql that changes data, it's a good idea to take a backup first
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Posts
    13
    If u create temporary tables to remove doubloons, then the issue of transaction & synchronization check comes in that leads to a better level of difficulty. A single line dynamic SQL should be the best choice that bypasses risks & does exactly what we need like bullet. Again, a single line SQL should be a better choice for cross database compatibility.
    Shahriar Kabir

Posting Permissions

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