469,645 Members | 1,174 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,645 developers. It's quick & easy.

Comparing Two Tables and Deleting duplicate

3
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!
Nov 11 '09 #1
7 12800
code green
1,726 Expert 1GB
A sub-query is probably the easiest to understand
Expand|Select|Wrap|Line Numbers
  1. DELETE FROM tableA WHERE email IN (SELECT email FROM tableB) 
or something like that.
But test first using
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM tableA WHERE email IN (SELECT email FROM tableB) 
[/code]
Nov 12 '09 #2
nhatto
3
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,IMPLICIT) for operation '='

I have no clue what that means? any idea?
Nov 12 '09 #3
nhatto
3
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 !
Nov 12 '09 #4
@code green
Work fine... thank you for great job
Sep 25 '14 #5
Worked for me, thank you...
Jun 17 '19 #6
You can try this, I hope it will assist you.

Expand|Select|Wrap|Line Numbers
  1. DELETE table1 
  2. FROM table1 
  3. INNER JOIN table2 ON table1.name = table2.name
  4. WHERE table1.id = table2.id
thank you.
Aug 5 '19 #7
Expand|Select|Wrap|Line Numbers
  1. DELETE a
  2. FROM a  -- first table
  3. INNER JOIN b -- second table
  4.       ON b.ID = a.ID
  5.       AND b.Name = a.Name
  6.       AND b.Foo = a.Foo
  7.       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.
Aug 19 '19 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

25 posts views Thread by David | last post: by
18 posts views Thread by Dan | last post: by
12 posts views Thread by NickName | last post: by
3 posts views Thread by Nathan Bloom | last post: by
5 posts views Thread by ken | last post: by
4 posts views Thread by HLCruz via AccessMonster.com | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.