how to transfer dataset from t2 to tarchive on a condtion and delete then from t2-CodePudding

I have 3 tables t1,t2 and tarchive, I want to transfer only records from t2 into tarchive that are not included in t1 and then delete from t2.

below is the solution I have but it doesn't work:

INSERT INTO tarchiv
WHERE (SELECT COUNT(t1.id) FROM t1 as a, t2 as b WHERE a.t1id = b.t2id) = 0;

DELETE FROM t2
WHERE (SELECT COUNT(idangeb0t) FROM t1 as a, t2 as b WHERE a.t1id = b.t2id) = 0;

can someone help me out with it?

CodePudding user response：

We can use the syntax INSERT INTO ... SELECT using a left join onto t1 and a condition where t1.id is null. This means that we do not insert any values which are in t1.
NB This is in mySQL. Depending on your RDBMS there may be modifications of the syntax INSERT INTO ... SELECT

UPDATE FOLLOWING COMMENT If we only want to delete the values not in t1 from t2 we can use

delete from t2 where id not in (select id from t1);

create table t1 (id int);
create table t2 (id int);
create table tarchive (id int);
insert into t1 values(1),(2),(3);
insert into t2 values(3),(4);

select * from t1;
select * from t2;
select * from tarchive;

insert into tarchive(id)
select t2.id 
from t2 
left join t1 on t2.id = t1.id
where t1.id is null;
delete from t2;

select * from t1;
select * from t2;
select * from tarchive;

| id |
| -: |
|  1 |
|  2 |
|  3 |

| id |
| -: |

| id |
| -: |
|  4 |

db<>fiddle here

CodePudding user response：

INSERT INTO tarchive(id) SELECT b.id FROM t2 AS b WHERE NOT EXISTS ( SELECT *FROM t1 AS a

WHERE a.id =b.id

) delete from t2 AS b WHERE NOT EXISTS ( SELECT *FROM t1 AS a

WHERE a.id =b.id

)

we have found a solution :)