connect 'jdbc:derby:fortunes';

-- =========================================================================
-- Set offense levels
-- =========================================================================
-- Since a given fortune could have multiple words, set the lowest
-- levels first.
update fortunes 
set    offense=1
where  id in
       (select f.id
        from fortunes f, offensive o
	where o.level=1
	and tutMatch(f.fortune, o.word)=1);

update fortunes 
set    offense=2
where  id in
       (select f.id
        from fortunes f, offensive o
	where o.level=2
	and tutMatch(f.fortune, o.word)=1);

update fortunes 
set    offense=3
where  id in
       (select f.id
        from fortunes f, offensive o
	where o.level=3
	and tutMatch(f.fortune, o.word)=1);

update fortunes 
set    offense=4
where  id in
       (select f.id
        from fortunes f, offensive o
	where o.level=4
	and tutMatch(f.fortune, o.word)=1);

update fortunes
set    offense=5
where  exists
       (select f2.id
        from fortunes f2, offensive o
	where o.level=5
	and tutMatch(f2.fortune, o.word)=1
	and f2.id=fortunes.id
	);
