Comparing Two Tables and Deleting duplicate

[nhatto]

Hello

I have Two Tables ( Table A and Table B)

Table A has one field : email
Table B has three Fields : First name, Last name , email

I want to check if the email in Table B is found in Table A, And if it is , then I want to delete the row ( First name, Last name , email ) from Table B that has that email.

any help is really appreciated and thank you in advance!

[code green]

A sub-query is probably the easiest to understand

DELETE FROM tableA WHERE email IN (SELECT email FROM tableB) 

or something like that.
But test first using

SELECT * FROM tableA WHERE email IN (SELECT email FROM tableB) 

[/code]

[nhatto]

Hello,

Thank you for your response

when i try to run the query, I keep getting the following error:

#1267 - Illegal mix of collations (latin1_swedish _ci,IMPLICIT) and (armscii8_bin,I MPLICIT) for operation '='

I have no clue what that means? any idea?

[nhatto]

I found why this is the error :

the reason was the Collation were not matching for both fields..

got it fixed.. and it worked beautifully

thank you so much !

[PreethiBugs]

@code green
Work fine... thank you for great job

[romiardisaja]

Worked for me, thank you...

[gosai jahnvi]

You can try this, I hope it will assist you.

DELETE table1 
FROM table1 
INNER JOIN table2 ON table1.name = table2.name
WHERE table1.id = table2.id

thank you.

[shivkumar1]

DELETE a
FROM a  -- first table
INNER JOIN b -- second table
      ON b.ID = a.ID
      AND b.Name = a.Name
      AND b.Foo = a.Foo
      AND b.Bar = a.Bar

That should do it... there is also CHECKSUM(*), but this only helps - you'd still need to check the actual values to preclude hash-conflicts.