How do I merge and delete existing primary key on new database?

I have 2 tables with same structures.

current_db = 521,892 rows and primary key is email

new_db = 575,992 rows and primary key is email

On new_db I want delete existing email address which already in curr开发者_运维技巧ent_db.

Question :

How do I compare & delete existing email on new_db and the results on new_db only be 54,100

Check the rows you want to delete -

SELECT n.* FROM new_db n
  JOIN current_db c
    ON n.email = c.email;

Delete them -

DELETE n
FROM new_db n
  JOIN current_db c
    ON n.email = c.email;

if indexes matches you can do it in one shot with something like:

delete from new_table where id in (select id from old_table) 

Otherwise you have to modify the query according to your matching fields

delete from new_table where id in (select id from old_table where oldtable.field = newtable.field)

Of course you have to pay attention on what you actually delete, my suggestion is to delete in two pass (creating a temporary table and changing the delete into an insert into temp_table) and then check if effectively everythings is correct.

(Under Oracle there's the minus operator which shows the difference between two recordset, hopefully there's something similar in your database enviroment).

minus op works like:

select * from table_a
minus 
select * from table_b

Starting with 2 identical tables, it gives only rows with different field values.

Is straightforward how to cross check with count(*) if everything is fine with the data. Don't know if for your RDBMS exists something alike, but I think that a fast search on google may help.

DELETE n
FROM new_db n
WHERE EXISTS
  ( SELECT *
    FROM current_db c
    WHERE c.email = n.email
  )