Как написать запрос (устранение "сирот")?

Question

[Wan-Derer]

Всем привет! Никак не соображу :(
Есть такая табличка:

В ней есть "базовые" записи (у которых) PARENT_ID is null и "дочерние" - у которых PARENT_ID = ID другой записи. Надо исключить из таблицы те записи, у которых PARENT_ID ссылается на несуществующий ID.
Пока сделал промежуточную таблицу с уникальными и не null PARENT_ID. Дальше мысли закончились :) Памагити!

Comments

[Дмитрий]

Выбрать всех parent_id для where id not in

[Akina]

Либо LEFT JOIN WHERE IS NULL, либо WHERE NOT EXISTS.

Дмитрий, WHERE NOT IN - самая медленная из альтернатив, лучше её не использовать.

Answer 1

[Rsa97]

DELETE `t1`.*
  FROM `table` AS `t1`
  LEFT JOIN `table` AS `t2` ON `t2`.`id` = `t1`.`parent_id`
  WHERE `t2`.`id` IS NULL

Answer 2

[Slava Rozhnev]

-- get records with wrong parents
select * 
from test
where 
	parent_id is not null 
	and not exists (
		select id from test as parents where parents.id = test.parent_id
	);
	
-- delete records with wrong parents
delete test.* 
from test
left join test as parents on parents.id = test.parent_id
where test.parent_id is not null and parents.id is null;

select * from test;

-- add foreign key to prevent missing parents
alter table test add foreign key (parent_id) references test(id);

run SQL online

Comments

[Wan-Derer]

Спасибо, но на Firebird предложенные варианты не работают. Видимо, там target не предполагает возможности join.

Попробую первым селектом сделать временную таблицу с "плохими" id и потом по ней поубивать записи в первой таблице :)