CODEWITHSUNDEEP
sqlsql-serverdatabasesql-delete
Johan Hjalmarsson
Johan Hjalmarsson

Reputation: 3493

Trying to get my delete statement to work

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.

Upvotes: 1

Views: 136

Answers (4)

Nilesh Nikumbh
Nilesh Nikumbh

Reputation: 302

Try this one:

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

Upvotes: 2

Jodrell
Jodrell

Reputation: 35716

Ok, I'd start by writing a query that selects the rows you want to delete,

SELECT
            *
    FROM
       Table1
    EXCEPT
    (
    SELECT
                t1.value
        FROM
                Table1 t1
            JOIN
                Table2 t2
                    ON t2.value = t1.value
        WHERE
                t2.i > 0
    );

See Fiddle

Then change the SELECT to a DELETE

DELETE Table1
    FROM
       Table1 t1
    WHERE
       t1.value NOT IN
    (
    SELECT
                t1.value
        FROM
                Table1 t1
            JOIN
                Table2 t2
                    ON t2.value = t1.value
        WHERE
                t2.i > 0
    );

See Fiddle

Upvotes: 1

paxdiablo
paxdiablo

Reputation: 881383

How about:

delete from table1 where value in
    (select value from table2 group by value having max(i) < 1)

Grouping table 2 by value and using having to detect where the maximum is less than 1 allows you to select the correct values for deletion from table 1.

having is basically a where clause which comes into play after aggregation so can be used with max and so on.

Here's a script to show it in action:

DROP TABLE TABLE1;
DROP TABLE TABLE2;
CREATE TABLE TABLE1 (VALUE INTEGER);
CREATE TABLE TABLE2 (VALUE INTEGER, I INTEGER);
INSERT INTO TABLE1 VALUES (5);
INSERT INTO TABLE1 VALUES (3);
INSERT INTO TABLE2 VALUES (5, 0);
INSERT INTO TABLE2 VALUES (5, 3);
INSERT INTO TABLE2 VALUES (3, 0);
INSERT INTO TABLE2 VALUES (3, 0);

SELECT * FROM TABLE1;
SELECT * FROM TABLE2;

DELETE FROM TABLE1 WHERE VALUE IN
  (SELECT VALUE FROM TABLE2 GROUP BY VALUE HAVING MAX(I) = 0);

SELECT * FROM TABLE1;
SELECT * FROM TABLE2;

The output of this script is shown below. First, the setting up of all the tables:

DROP TABLE TABLE1; DROP TABLE TABLE2;
    TABLE1 DROPPED
    TABLE2 DROPPED
CREATE TABLE TABLE1 (VALUE INTEGER);
    TABLE1 CREATED
CREATE TABLE TABLE2 (VALUE INTEGER, I INTEGER);
    TABLE2 CREATED
INSERT INTO TABLE1 VALUES ((5), (3));
    INSERTED 2 ROWS
INSERT INTO TABLE2 VALUES ((5, 0), (5, 3), (3,0), 3, 0));
    INSERTED 4 ROWS

And display them to ensure they're as expected:

SELECT * FROM TABLE1; SELECT * FROM TABLE2;
    VALUE
    -----
      5
      3
    VALUE   I
    -----   -
      5     0
      5     3
      3     0
      3     0

Then run the command to get rid of the relevant rows:

DELETE FROM TABLE1 WHERE VALUE IN                          
  (SELECT VALUE FROM TABLE2 GROUP BY VALUE HAVING MAX(I) = 0);
    DELETED 1 ROW

And you can see that the 3` row has disappeared from table 1 as desired:

SELECT * FROM TABLE1; SELECT * FROM TABLE2;
    VALUE
    -----
      5
    VALUE   I
    -----   -
      5     0
      5     3
      3     0
      3     0

Upvotes: 0

cosmos
cosmos

Reputation: 2303

I dont know why you are using min, instead u should use max:

try this

     DELETE FROM Table1
   WHERE Table1.value1 = Table2.value1
   and (SELECT MAX(Table2.i) FROM Table2
   WHERE Table1.value1 = Table2.value1) < 1;

Upvotes: 1

Related Questions