dealing with double records

gtk · #1 (**permalink**) 02-15-05, 10:48

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 ?

r937 · #2 (**permalink**) 02-15-05, 10:56

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

SKJoy · #3 (**permalink**) 02-15-05, 22:40

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech