Trying to get my delete statement to work

Question

I want to remove rows from a table, based on a column from another table as such:

Table1:            Table2:
value              value, i

If table2.i is less than 1, delete corresponding row from table1 (but keep it in table2).

The problem is that value isn't unique, so if I have this for exampe:

 Table1            table2
+-------+        +-----------+
| value |        | value | i |
+-------+        +-----------+
|   5   |        |  5    | 0 |
|   3   |        |  5    | 3 |
+-------+        |  3    | 0 |
                 |  3    | 0 |
                 +-----------+

Value 3 should be deleted from table1 (since all occurrences in table2 has i<1) but value 5 should stay(because of i=3 row in table2)

My code so far (doesn't work):

DELETE FROM Table1, Table2
WHERE (SELECT MIN(Table2.i) FROM Table1, Table2
       WHERE Table1.value = Table2.value) < 1;

Problem is: since my subquery returns min for ALL rows, everything gets deleted.

And I can't use "group by" in my subquery because then my comparison isn't allowed.

Answer 1

Try this one:

DELETE FROM Table1 
WHERE NOT EXISTS(SELECT 1  
                   FROM Table2          
                  WHERE Table2.i > 0 
                    AND Table2.value = Table1.value)