I ran across a scenario today in which I needed to purge records from a table based on what was in a similar table in another database.
I had 2 user tables in different databases but the tables had some overlapping data. For example, the first user table is in a database called "database1" and the other is in a database called "database2." Some example data is below.
1Jack 3Jim 5Mary 7Sue 9Ron 11Phil
1Jack 2Creed 3Jim 4Dave 5Mary 6Bob
Now, to purge our user table in database1 of any records that match the user in database2, we first need to figure out how to write a join statement across multiple database. Here's how it's done.
SELECT u1.* FROM database1.users u1 LEFT JOIN database2.users u2 ON u1.id=u2.id WHERE u2.id IS NOT NULL;
The above query will will join the 2 tables based on the primary id and only return records for the first user table if a match is found in the second user table.
It's always a good idea, before running a DELETE statement to first test your query with a similar SELECT statement. Once you've confirmed your SELECT statement is returning the correct records you can modify it to delete those records returned.
DELETE u1.* FROM database1.users u1 LEFT JOIN database2.users u2 ON u1.id=u2.id WHERE u2.id IS NOT NULL;
When I first tried the DELETE query I kept getting an error because I started the query with
DELETE FROM. I'm guessing that made the table to delete from ambiguous, because it worked fine when I made the delete statement more specific with
DELETE u1.* FROM.