0

I have two MySQL tables. The first table has a cron job running where it gets updated every five minutes. When the cron job runs, it moves all the data from the first table into the second table (an archive table), truncates the first table and then updates the information from a CSV file that's constantly being updated at a certain URL. I made the tables by copying the structure from one table to the next (so they are identical in structure).

Here's the code I'm running, which works:

$sql = "
INSERT INTO table2 (id, time, size, location, county, state, lat, lon, comments)
SELECT id, time, size, location, county, state, lat, lon, comments FROM table1";
$result = $mysqli->query($sql);

The problem is, the cron job runs so often that it's not always going to be completely updated so it would make duplicate entries into table2. Here's the code I was trying to use, but it doesn't seem to be working:

$sql = "
INSERT INTO table2 (id, time, size, location, county, state, lat, lon, comments)
SELECT id, time, size, location, county, state, lat, lon, comments FROM table1
ON DUPLICATE KEY UPDATE id=id
";
$result = $mysqli->query($sql);

I've made sure the keys for both tables are the same (id).

Do these operations not working together or is my syntax wrong somewhere along the line? I wanted to try to run these operations all in one shot instead of updating the table and then running a different operation to get rid of the duplicates afterwards.

Thanks for your help!

liyakat
  • 11,825
  • 2
  • 40
  • 46
MillerMedia
  • 3,651
  • 17
  • 71
  • 150

1 Answers1

0

You can use insert ignore query to avoid data duplication. But each time you are selecting the maximum entries. Some times the possibility exist to truncate new entries from table1

$sql = "
INSERT IGNORE INTO table2 (id, time, size, location, county, state, lat, lon, comments)
SELECT id, time, size, location, county, state, lat, lon, comments FROM table1";

You can follow this approach $primary_id = last_updated_primary_key;

//add where condition int the where query
$sql = "
INSERT INTO table2 (id, time, size, location, county, state, lat, lon, comments)
SELECT id, time, size, location, county, state, lat, lon, comments FROM table1 WHERE id> < last_updated_primary_key >";

delete the old entries using the primary existing key

DELETE FROM table1 WHERE id < last_updated_primary_key;

overwrite the latest primary key

$primary_id = last_updated_new_primary_key;

You can use triggers to update new entries dynamically from one table to another table

http://dev.mysql.com/doc/refman/5.0/en/triggers.html

Sundar
  • 4,580
  • 6
  • 35
  • 61