The other day I needed to compare two tables and see what data was in one but not in the other.
table_a is my main table that I want to update and table_b is my storage table that I have my updates in. The query below will return all records that are in table_b, but no in table_a.
SELECT table_b.* FROM table_b
LEFT JOIN table_a ON table_b.id = table_a.id
WHERE table_a.item_id IS NULL
The you can take those results and use them to INSERT the missing records.
This is the best way I can think of. If anyone has a better way, please leave a comment on how you would recommend.
Similar Posts:
February 8th, 2007 @ 4:57 pm
This is palmetto from digg.. thanks for the info about myphpadmin and mysql.
@palmetto – if you’ve already got a site and a company with PHP & MySQL hosting your site, hop on over to http://www.linein.org/blog/ and post a comment with your email address. I’d be more than happy to help. Both are really easy to setup and get started.
thanks for the help.. I will be learning now.
February 22nd, 2010 @ 6:30 am
Thanks for this little article, we have used this to compare commercial gas prices for our customers.
March 3rd, 2010 @ 2:45 pm
This just saved my butt. A winner is you!
March 16th, 2010 @ 1:17 pm
hi,
If you want more options, you can also use a third party tool, like Nob Hill’s compare :
http://www.nobhillsoft.com/NHDBCompare.aspx
April 12th, 2010 @ 1:31 pm
Thanks for that useful piece of code. It saved my time
A little smile in right upper part of the page is funny =)
May 13th, 2010 @ 1:53 pm
SELECT * FROM tblA
WHERE tblA.ID
NOT IN (
SELECT tblB.ID
FROM tblB
WHERE tblA.ID=tblB.ID
)