ansaurus

Question

mass replace of values in more tables by a highest value

Answer 1

+1 A:

Could accomplish this fairly simple using a transaction that nukes the whole table.

BEGIN;
 SELECT own_email, max(own_Id) INTO temptable FROM table GROUP BY own_email
 TRUNCATE table;
 INSERT INTO table SELECT * FROM temptable;
 DELETE temptable;
COMMIT;

But, that Isn't the best way -- you can also do it by deleting just the right rows.

DELETE FROM table AS d WHERE EXISTS (
  SELECT own_id FROM table AS t
  WHERE NOT EXISTS (
    SELECT max(own_id) FROM table AS i
    GROUP BY own_email
    WHERE t.own_email = i.own_email
  )
  AND d.own_id = t.own_id
  AND d.own_email = t.own_email
);

Here we delete all own_ids, that aren't the max(own_id) for an E-mail.

This can be accomplished with NOT IN (understanding the caveat on nulls), or an anti-join on the table being deleted from

Evan Carroll 2010-06-14 18:31:11

Answer 2

A:

delete t1 from table t1, table t2
where t1.own_email= t2.own_email
and t1.own_id < t2.own_id

update othertable, table
set othertable.own_id= table.own_id
where othertable.own_email= table.own_email;

Gary 2010-06-14 18:31:20

ansaurus

tags:

views:

answers:

mass replace of values in more tables by a highest value

related questions