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

[chazzy69]

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

[Atli]

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.

[chazzy69]

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 -

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 :)

[Atli]

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...

CREATE TABLE t1Backup SELECT * FROM t1;
TRUNCATE t1;
 
INSERT INTO t1 VALUES /* All your new data */;
 
SELECT t1.id
FROM t1 LEFT JOIN t1Backup
  ON t1.id = t1Backup.id
WHERE t1Backup.id IS NULL;
 
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.

[chazzy69]

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

WHERE t1Backup.id IS NULL

;

could i change it to say perhaps-

WHERE t1Backup.id1 IS NULL AND t1backup.id2 = "to some value" AND t1backup.id3 = "to some other value"

;

??

Thanks for the help btw :)

[chazzy69]

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