Need help writing an SQL query to remove bounced emails

[kh99]

Originally Posted by howarde

UPDATE user LEFT JOIN bounce USING (email) 
   SET user.email = ''
   WHERE bounce.email <> '';

I just ran this on a test database and it does remove the bounce emails from the user table, and leave the field blank.

Let us know what happens. By the way, I don't see any problem with the query you posted above but I wanted to clarify what I mentioned before - I know that when a query starts with "UPDATE bounce LEFT JOIN user..." for instance, it sounds like you're updating the bounce table. But you can think of the "bounce LEFT JOIN user USING (email)" part as a virtual table containing fields from both, and if you then SET fields from the user table you'll be updating the user table and not bounce, even though bounce was mentioned first. And as I mentioned, the difference is that in the "bounce left join user" virtual table will contain one row for each row from bounce, whereas "user left join bounce" will have one row for each row in user (with bounce.email NULL where there was no match).

[howarde]

Yes... you are correct on the query. Works the same either way.

Thanks for the lesson.

Single table queries are easy for me, but the whole join thing makes it more complex, and I'm not writing queries every day.