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.
database1.users
IDName
1Jack 3Jim 5Mary 7Sue 9Ron 11Phil
database2.users
IDName
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.
Comments
Join query from multiple-databases
Hi Nathan,
I am running a website with PHP/MySQL and this will be a growing eBusiness in future. We have a limitation on our host that doesn't allow the db size to be more than 100MB. For this reason I'm designing to put a large table in a separate database for ease of maintenance in future (have bigger size, backing-up, archiving and making union etc)
As there is a relation between other tables and this one, do you think I am loosing any performance retrieving my join queries? Will they be much faster if I use all related tables in one database?
Do you have any suggestions for this architecture?
Thanks
I'm not too sure about the
I'm not too sure about the performance implications of joining tables across databases. That's outside the scope of my expertise.
plan to segregate tables in multiple databases
Hi Milad ,
As per your post i like to know your experience related to the performance when you have created multiple databases to maintain max limit of database.
I am working on a project which has a huge database. i want to separate the tables to multiple databases. is that affects the performance or it will decrease the response time.
is it possible to make relation between the attribute belonging from one DB with the attribute of other DB?
Pl send me the detail suggestion and the way how i can do it.
Thanks in advance.
Do you know that graco
Do you know that graco duoglider double stroller is an excellent double stroller for your children. It is perfect for both infants and for pre-schoolers.
mail me the details on
mail me the details on bhmb2000@rediffmail.com
This blog is very
This blog is very interesting. But if you want to extract the herbal aroma and flavor molecules from the similar ingredients you may that volcano vaporizer . This device has a digital and analog temperature control and two valves options by which it fills the balloon.
Coach Outlet is designed so
Coach Outlet is designed so elegance and it is cheap sale Coach Outlet Online. The handbags are one of the most popular designer handbags on the Coach Outlet Online today. Coach Colette Handbags which is one of the classic styles of Coach Outlet. Colorful appearance and beauty accessories are the point of Coach Purses Outlet. These Coach Colette Bags are must-have from this classic Coach family. The traditional stylish Cheap Coach Handbags with gorgeously and luxuriously dotted coach print appearance, updates an Coach Factory extremely clean and extremely modern day time style research to accomplish Coach Outlet Online a alluring stylish coach bags. Every professional woman needs a good handbags. No matter what your industry or position. Colorful appearance and beauty accessories are the point of Coach Colette Bags.Every professional woman needs a good handbags. No matter what your industry or position, a Coach Factory Outlet is essential for anyone to be taken seriously at the condition. Welcome to our website and we will provide the best Coach Colette Handbags and best service for dear customers.
True Religion Outlet
As we all know, the http://nikeoutletcheap.com/ end of a single number is not much, so most of them are fake.So how to identify genuine and fake foreign trade last single has become a headache. the original single goodsThe original http://nikeonlines.com/ single shipment of foreign trade in the true sense of the end of a single cargo. All of these commodities with the fabric / accessories http://truereligioncheapoutlete.com/ type version is provided by foreign brands in the domestic manufacturers out of the best equipment and management, these jeans, if the quality is perfect, it http://www.truereligiononlinecheap.com/ will become really expensive goods in the store. However, due to the very strict quality inspection, there is always a small part of the brand declined, and returned, so it was a real sense of the foreign trade last single, these products are often not low quality, a small number, due to defects, usually cut http://truereligiononlinestore.com/ standard by manufacturers generally to the market is three to one-fifth of the price of the genuine.
discount oakley sunglasses
2012 sunmmer is coming,oakley eyewear hot sale on discount oakley sunglasses online,every year cheap oakley is welcome by young people,it wears cool on the summer holiday. http://www.discountoakleyeyewear.com
Post new comment