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

user_16438295 0
Viewed 24

enter image description here

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?

sql
2 Answers

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

Admin1

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 :)

user_164382950
Related