Mercurial > hgrepos > hgweb.cgi > s4
view scripts/clean-orphaned.sql @ 1013:1ffaa8b2b1bf draft
Modify blog comment form layout
author | HIROSE Yuuji <yuuji@gentei.org> |
---|---|
date | Tue, 11 Jul 2023 10:25:21 +0900 |
parents | 4bc9494c00ff |
children |
line wrap: on
line source
-- 親のないblogを探す CREATE TEMPORARY VIEW IF NOT EXISTS orphanedblog AS SELECT blog.id,val FROM blog JOIN blog_s bs ON blog.id=bs.id AND key='owner' WHERE val NOT IN (SELECT gname FROM grp) AND val NOT IN (SELECT name FROM user); SELECT * FROM orphanedblog; SELECT id FROM orphanedblog; -- 削除処理 SELECT count(*) FROM article; DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog); SELECT count(*) FROM article; -- 親のないarticleを探す これがFOREIGN_KEYしてなかった!!! CREATE TEMPORARY VIEW IF NOT EXISTS orphanedarticle AS SELECT id FROM article WHERE blogid NOT IN (SELECT id FROM blog); SELECT count(*) FROM orphanedarticle; SELECT id FROM orphanedarticle; -- 削除処理 SELECT count(*) FROM article; DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle); SELECT count(*) FROM article;