Mysql – sync 2 tables in a single database in thesql with no duplicacy

developmentinnodbMySQLscripting

I have 2 tables in a single database of MySQL.

The structures are like this:


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

Best Answer

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.