sync 2 tables in a single database in mysql with no duplicacy I have 2 tables in a single database of MySQL.

The structures are like this:


mysql&gt; desc main_tbl;
+-----------------+--------------+------+-----+-------------------+----------------+
| Field           | Type         | Null | Key | Default           | Extra          |
+-----------------+--------------+------+-----+-------------------+----------------+
| mid             | int(11)      | NO   | PRI | NULL              | auto_increment |
| acc             | varchar(255) | NO   | MUL | NULL              |                |
| device          | varchar(255) | NO   |     | NULL              |                |
| start           | datetime     | NO   |     | NULL              |                |
| end             | datetime     | NO   |     | NULL              |                |
| login_user      | varchar(255) | NO   |     | NULL              |                |
| date_added      | datetime     | NO   |     | CURRENT_TIMESTAMP |                |
+-----------------+--------------+------+-----+-------------------+----------------+

mysql&gt; desc report_backup;
+--------------+--------------+------+-----+-----------+----------------+
| Field        | Type         | Null | Key | Default   | Extra          |
+--------------+--------------+------+-----+-----------+----------------+
| rid          | int(11)      | NO   | PRI | NULL      | auto_increment |
| mid          | int(11)      | NO   | UNI | NULL      |                |
| times        | varchar(255) | NO   |     | 2 minutes |                |
| account      | varchar(255) | NO   |     | NULL      |                |
| dev          | varchar(255) | NO   |     | NULL      |                |
| start        | datetime     | NO   |     | NULL      |                |
| end          | datetime     | NO   |     | NULL      |                |
| logged_user  | varchar(255) | NO   |     | NULL      |                |
| added_time   | datetime     | NO   |     | NULL      |                |
+--------------+--------------+------+-----+-----------+----------------+

The first table is populated with user input, where records can be added,deleted.

The second table is used for backup where whenever a record is inserted in the 1st one, it will be automatically copied.

So I used a  query like this to run everytime any record is inserted in the 1st table;

INSERT INTO report_backup(mid, account, dev, start, end, logged_user, added_time) SELECT mid, acc, device, start, end, login_user, date_added from main_tbl; 

But this seems not working due to the mid is unique, which I made to avoid duplicate entry.

Any suggested solution will be appreciated.

Thanks
The mysql Syntax for insert data is :

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );


In your query you just missed values and you must pass where condition to select a specific row, in your case the last insert id:

 INSERT INTO report_backup(mid, account, dev, start, end, logged_user, added_time) SELECT mid, acc, device, start, end, login_user, date_added from main_tbl;

Correct one is :

INSERT INTO report_backup(mid, account, dev, start, end, logged_user, added_time) VALUES (SELECT mid, acc, device, start, end, login_user, date_added from main_tbl WHERE mid = 'Your Last Inserted Value');


Hope my answer is clear enough to fix your issue.