CODEWITHSUNDEEP
sqlite
alofgran
alofgran

Reputation: 477

How do I add a delete statement to this code?

I need to understand how to add a statement that will delete the results of the following query:

I understand that a DELETE statement with a WHERE clause would normally be used, but because I'm SELECTING two different columns, the where clause doesn't accept the comma. I've not yet been able to figure out how to turn this into a CTE (but maybe that's overkill?) and then call it in a DELETE statement later (assuming that's even an option). Examples always have the DELETE FROM..., or WHICH statements, neither of which seem implementable under this code. Do I have to rewrite my code so it includes a WHICH statement?

SELECT     field1,  field2
FROM       table
GROUP BY   field1, field2
HAVING     SUM(field3) IS NULL

Expecting to be able to institute a DELETE statement to delete the results of the query.

Upvotes: 1

Views: 29

Answers (1)

MikeT
MikeT

Reputation: 56948

I believe that you could use (assuming that the table is not a WITHOUT ROWID table) :-

DELETE FROM mytable WHERE rowid IN (SELECT rowid FROM mytable GROUP BY field1, field2 HAVING SUM(field3) IS NULL);

An alternative using a CTE (where the CTE has been given the name deletions) would be :-

WITH deletions(rowid) AS (SELECT rowid
FROM       mytable
GROUP BY   field1, field2
HAVING     SUM(field3) IS NULL
)
DELETE FROM mytable WHERE rowid IN (SELECT rowid FROM deletions);
  • note that mytable has been used as the table name instead of table.

Considering the comment

Primary key is field1

then :-

 DELETE FROM mytable WHERE field1 IN (SELECT field1 FROM mytable GROUP BY field1, field2 HAVING SUM(field3) IS NULL);

could be used, this would then work whether or not the table is defined as a WITHOUT ROWID table, a similar change could be applied to the CTE version.

Notes

Using GROUP BY on a PRIMARY KEY, as it is UNIQUE, will result in as many groups and therefore rows, as there are rows. Effectively the query could be SELECT field1 FROM mytable WHERE field3 IS NULL and therefore the deletion could simply be DELETE FROM mytable WHERE field3 IS NULL.

If this were not the case and field1 was not the PRIMARY KEY, then the complication is that values per group that are not aggregated values are values from an arbitrarily selected row. In short you would delete 1 from a number of the rows that where grouped.

Upvotes: 1

Related Questions