Hello, my problem is as follows: I

[halvar]

Hello,
my problem is as follows: I'd like to delete all those data records of a certain table(let's call it Table A) where the email field has the identical entry as the email field of another table (let's call it Table B). I've finished the first step already, which is to seperate all records with identical email adresses. But I'm stuck when it comes to deleting those records off Table A. Does anybody know a nice Syntax from there? Thanks,
Michael

 

[gheist]

delete from a where a.email in (select email from b)

 

[halvar]

thanks for the hint but mysql is not accepting 'in' in this case. Also I tried :

DELETE a.* FROM a, b where a.email = b.email

but this does not work either. What else could one try?

 

[gheist]

CREATE TEMPORARY TABLE zzz (id INT );
INSERT INTO zzz SELECT a.id FROM a,b WHERE a.email=b.email
DELETE FROM a WHERE a.id=zzz.id
DROP TABLE zzz

 

[gheist]

a.id - primary key for table a

 

[halvar]

Sorry, still a problem with the delete command:

DELETE FROM a WHERE a.id=zzz.id (does not know the zzz.id)

maybe you have another idea - thanks

 

[gheist]

BEGIN WORK

statements

COMMIT WORK

 

[halvar]

Hi Gheist,
Thanks for your patience but sorry, still would not do it.

 

[gheist]

DELETE FROM a WHERE a.id in (select id from zz)

 

[halvar]

this time
MySQL said:


'You have an error in your SQL syntax near 'select id from zzz)' at line 1'

quite a tricky one, this is