473,320 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How to compare entries in 2 seperate tables and store results in a 3rd table

196 100+
Ok heres what im trying to achive i am constantly updating the information in a table but know i what know if the data im replacing it with is new, i.e. whether or not i already had the data in the table.

So my method for this is to first copy the information to be updated into another table (the 2nd table), then allow the information in the first table to updated (via deleting the current information and re-entering it).

Then i what to compare the two tables to see if there is any new rows that where not there before updating the data in the first tables. (that is i want to compare both table 1 and 2 at this point).

And this is where im struggling to find a method for comparing the two tables, to identify any new rows.

So i was wondering if anyone had any ideas on how to compare these two seperate tables.

Note: These tables are all in the same MYSQL database,

Any help would be greatly appreciated
May 24 '09 #1
5 3027
Atli
5,058 Expert 4TB
I don't get it.

You have table A, which has a bunch of rows.
Then you get table B, which might have all the old rows as well as new once.

And you need to figure out which rows in table B are new compared to the rows in table A.

Is that right, or am I not getting it?

If not, please explain this a bit better, preferably with examples.
May 24 '09 #2
chazzy69
196 100+
Ok to start with i have a single table in which all information is constantly being updated, currently i no way to be able to tell when im adding new rows to a table or just the old ones, my databases would be to big if i just tried to store all data.

So what i need to do now is to determine when i add a new row to a table but all the old rows are deleted before the new rows are added, this is to save space as i explained above.

Therefore my method for getting around this was to make a backup of the rows i was deleting and then comparing it against the new rows that i add, so in this way it tell if there are any rows that i have just added that are new that is they where not there before i deleted the old data.

So i have figured out how to transfer the data to another table before i delete the old data. My current problem is being able to determine the difference in the old and new data between the tables that is is there a row contained in the orignal table that was not there before i updated the rows.

I did find a bit of code -

Expand|Select|Wrap|Line Numbers
  1. SELECT t1.id FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL
But when i implemented it it seemed the where clauses are not working properly cause i limited it to compare 30 rows form each and see if there are any that are different but i found that it got 933 differences instead of 3 differences that actually exist.

Anyway i dont have to use that code but any alternate methods would be great,

Hope that cleared it up a bit :)
May 24 '09 #3
Atli
5,058 Expert 4TB
Ok.

So you are essentially deleting the old table and rebuilding it with new data, most of which will be the same as the old data.

This is how I am picturing this...
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE t1Backup SELECT * FROM t1;
  2. TRUNCATE t1;
  3.  
  4. INSERT INTO t1 VALUES /* All your new data */;
  5.  
  6. SELECT t1.id
  7. FROM t1 LEFT JOIN t1Backup
  8.   ON t1.id = t1Backup.id
  9. WHERE t1Backup.id IS NULL;
  10.  
  11. DROP TABLE t1Backup;
Which would give you the IDs of all the new rows.

Note that if you don't TRUNCATE or DROP the main table before recreating it the SELECT statement will return all the rows, as the IDs will not be reset.
May 24 '09 #4
chazzy69
196 100+
Ok this sounds good but i was wondering becuase im only deleting part of my table and updating at a time perhaps i could limit or restrict what the function uses to compare with for example in the code u used above

Expand|Select|Wrap|Line Numbers
  1. WHERE t1Backup.id IS NULL
;

could i change it to say perhaps-

Expand|Select|Wrap|Line Numbers
  1. WHERE t1Backup.id1 IS NULL AND t1backup.id2 = "to some value" AND t1backup.id3 = "to some other value"
;

??

Thanks for the help btw :)
May 24 '09 #5
chazzy69
196 100+
Ok don't worry about my last post i have figured out how to get it working.

Just wondering if when you see the results from the comparison is it possible to show all the colums of the row that was different because at the moment all it outputs is the different field i used to compare with.

i.e. if im comparing - t1.id1 to t2.id1

it will output only .id1 rather than the entire row like .id1 | .id2 | .id3

Hope that makes sense,

Also is it possible to store results from a query into a table??

Thanks again for all the help
May 25 '09 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Megan | last post by:
Hi everybody- I'm helping a friend with a music database. She has an old one and is creating a new one. She wants to compare records and fields in the old database with records and fields in the...
8
by: Vincent | last post by:
has any one seen a program to compare mdbs'. I have ran into a few of them, but none seem to really do that job. Basically what I need to do is, take 2 access mdb's and check the differences...
9
by: VMI | last post by:
I have two tables and I want to compare these two tables with a query( ie. "select * from A where B.key = A.key") and the result will be stored in a 3rd table (table C). is this possible? If...
1
by: Mark | last post by:
by m.r.davies I have 2 tables on seperate Db's (and servers) I want to use a datareader on the first table to pick the booking ref, and then use that booking ref to query the 2nd DB when i have...
0
by: Frank | last post by:
Hello, Developing an app where the user fills out a sometimes quite lengthy form of chkboxes, txtboxes, radbtns, etc. User responses are saved to a mySql db, which the user can later edit. When...
4
by: Lamis | last post by:
Hi, what is the best way to compare 2 haschtables contatining objects. the objects has 2 property, name & value. I need to print out the differences -- LZ
5
by: Edd E | last post by:
Hi, I have a database to store my analyses (Access 2002, WinXp), the basic structure is: TABLE 1 = Sample Info TABLE 2 = Analysis type 1 TABLE 3 = Analysis type 2 TABLE 4 = Analysis type 3 ...
0
by: TechnoAtif | last post by:
<?php include "dbconnect.php"; include "commonFunc.php"; ?> <!----------------------------------> <table width="80%" border="1" cellpadding="2" cellspacing="0"> <tr > <td...
1
by: cssExp | last post by:
Hi, Assume i have two tables. "accounts" and "accounts_logs" "accounts" (accid, type) "accounts_log" (accid, ip, timestamp) Here i want to choose a radom 3 accids from "accounts" if...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.