Insert not existed records into Mysql table

Question

Hello in our system we lost one acc and we need to retreive it. Luckly we have a backup of database. Now im trying to add records from backup mysql database to current. But i have one problem. when i try to insert data i get error "ID duplication". That what i get when export table from backup.

    INSERT INTO `anketu_perziuros_mine` (`id`, `anketa`, `kada`, `timemark`) VALUES
(955009, 498044, 1443021887, '2015-09-23 18:24:47'),
(147188, 498044, 1443018663, '2015-09-23 17:31:03'),
(948120, 498044, 1443017899, '2015-09-23 17:18:19'),
(958152, 498044, 1442954185, '2015-09-22 23:36:25'),
(888916, 498044, 1442863283, '2015-09-21 22:21:23'),
(782244, 498044, 1442839575, '2015-09-21 15:46:15'),
(827707, 498044, 1442746875, '2015-09-20 14:01:15'),
(869393, 498044, 1442683453, '2015-09-19 20:24:13');

Im totally new in mysql. I tried many ways(from tutorials) all i have failed. How to implement IF NOT EXISTS, or there is other solutions?

https://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table — Alex, Mar 22 '18 at 11:13
It sounds like the backup you are using is not in sync with the current database, because there are duplicate records. — Tim Biegeleisen, Mar 22 '18 at 11:16

score 0 · Answer 1 · answered Mar 22 '18 at 11:25

Here is an illustration on how to achieve restoring the lost data. See Demo on SQL Fiddle.

-- suppose you have a table 
create table test (
  id int not null primary key,
  val int not null
);

-- with these data
insert into test values
(1,1),
(2,2),
(3,4),
(4,90);

-- lets assume you lost these data from the test table
delete from test where id in (1,4);

-- now you want to restore the lost data from your backup
-- do the following.

-- create a temporal table with schema of test table
create table test2 as 
select * from test limit 0;

-- insert backup data into the temporal table
insert into test2 values
(1,1),
(2,2),
(3,4),
(4,90);

-- copy backup data from temporal table into the real table
insert into test
select * from test2 b
where not exists (select null from test a where a.id=b.id);

-- drop backup data
drop table test2;

Insert not existed records into Mysql table

1 Answers1