473,380 Members | 1,243 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,380 software developers and data experts.

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 14066
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

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

Similar topics

2
by: Bill | last post by:
I have an entry form allowing customers to enter up to 15 skus (product id) at a time, so they can make a multiple order, instead of entering one sku, then submitting it, then returing to the form...
25
by: David | last post by:
I've got a 50k main.css file that's referenced by a load of sites. Each of these sites also has a site.css file that modifies certain styles defined in main.css. Changing the colour of borders,...
18
by: Dan | last post by:
hello, I would to know if it is possible to delete an instance in an array, The following does not allow me to do a delete. I am trying to find and delete the duplicate in an array, thanks ...
12
by: NickName | last post by:
I have the following situation. One set of data has 274 rows (set2) and anther has 264 (set1). Both data sets are similar in structure as well as values for both of them were extracts from the...
3
by: Nathan Bloom | last post by:
Hi, I have a data entry form (access 2000) that also allows the user to add, update, and delete records from the form. The Delete action is carried out in an event procedure and has the...
5
by: ken | last post by:
Hi, Do Junction Tables need ID fields with Primary keys? For example if you store employee ID, employee name, Company ID, Company name. As you can see no field here can be a primary key since all...
5
by: Manish | last post by:
The topic is related to MySQL database. Suppose a table "address" contains the following records ------------------------------------------------------- | name | address | phone |...
4
by: HLCruz via AccessMonster.com | last post by:
I am working with a database that has client information separated in to 4 related tables - tFolder, tAddress, tEmail, tPhone number. In addition there are related tables tGifts and tCalls. The...
1
by: jignasha1234567 | last post by:
hi, how can i delete data from one table which is similar to another data in another table having same coloumn name. suppose i have one table name test1 and another table named test2 . ...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.