How to delete specific entries in a database?

Question

Let's say there are three columns in a table: A, B and C. In column A there are employees, in column B entry date and in column C entry value. Some employees have three entries per day, some two entries and some just one. What would be a SQL script to delete these entries where an employee has only one entry per day?

I'm using Firebird 2.5, so not all newest commands available.

I was trying this:

DELETE FROM my_table AS t1
WHERE (
    SELECT COUNT(*)
    FROM my_table AS t2
    WHERE t1.employee = t2.employee AND t1.entry_date = t2.entry_date
) = 1;

The script gets stuck, maybe due to large database?

Answer 1

It is possible that your statement falls foul of the unstable cursor problem present in Firebird 2.5 and earlier (fixed in Firebird 3.0), though I can't readily see how. Possibly trying a different statement might help, but there is no guarantee these alternatives might not run into the same problem. The only "real" solution to these problems is upgrading to Firebird 3.0.

You could try and use SINGULAR for this:

delete from MY_TABLE as T1
where singular (
  select * 
  from MY_TABLE as T2
  where T2.EMPLOYEE = T1.EMPLOYEE and T2.ENTRY_DATE = T1.ENTRY_DATE
)

Singular will check if a subquery produces one and only one row.

Another option might be to add an ORDER BY to the delete statement. This is a bit of an esoteric option, but might result in a different statement plan that doesn't trigger the unstable cursor issue:

delete from MY_TABLE as T1
where ...
order by T1.EMPLOYEE, T1.ENTRY_DATE

(You may need to play with which columns to order, and maybe direction.)

As you ask if it is possible to delete in smaller chunks, it is. You need to add the ROWS clause to the statement:

delete from MY_TABLE as T1
where ...
rows 1000

(This will delete the first 1000 matching rows.)

Or combined with ORDER BY:

delete from MY_TABLE as T1
where ...
order by T1.EMPLOYEE, T1.ENTRY_DATE
rows 1000

Another option would be to use an EXECUTE BLOCK to try and tackle it procedurally (though, again, it might run into the same problem):

execute block
as
declare EMPLOYEE type of column MY_TABLE.EMPLOYEE;
declare ENTRY_DATE type of column MY_TABLE.ENTRY_DATE;
begin
  for select EMPLOYEE, ENTRY_DATE
      from MY_TABLE
      group by EMPLOYEE, ENTRY_DATE
      having count(*) = 1
      into EMPLOYEE, ENTRY_DATE do
  begin
    delete from MY_TABLE 
      where EMPLOYEE = :EMPLOYEE and ENTRY_DATE = :ENTRY_DATE;
  end
end

If that fails as well, you might have to resort to using a global temporary table as the staging ground for your delete:

First create and commit the GTT:

create global temporary table MY_TABLE_DELETE (
  -- I took a guess at data types
  EMPLOYEE integer not null,
  ENTRY_DATE date not null,
  constraint PK_MY_TABLE_DELETE primary key (EMPLOYEE, ENTRY_DATE)
)
on commit delete rows;

Then start a transaction:

-- Populate the GTT with the identifiers for the rows to delete
insert into MY_TABLE_DELETE (EMPLOYEE, ENTRY_DATE)
  select EMPLOYEE, ENTRY_DATE
  from MY_TABLE
  group by EMPLOYEE, ENTRY_DATE
  having count(*) = 1;

-- Actually delete the rows
delete from MY_TABLE T1
where exists (
  select *
  from MY_TABLE_DELETE
  where EMPLOYEE = T1.EMPLOYEE and ENTRY_DATE = T1.ENTRY_DATE
)

and commit the transaction.

If you were using Firebird 3.0 or higher, you would also be able to use MERGE (the DELETE option is not available in Firebird 2.5):

merge into MY_TABLE as T1
using (
  select EMPLOYEE, ENTRY_DATE
  from MY_TABLE
  group by EMPLOYEE, ENTRY_DATE
  having count(*) = 1
) as T2
on T1.EMPLOYEE = T2.EMPLOYEE and T1.ENTRY_DATE = T2.ENTRY_DATE
when matched then delete;

Answer 2

DELETE FROM my_table AS T1 
WHERE NOT EXISTS
(SELECT employee FROM my_table AS T2 
WHERE T1.entry_value <> T2.entry_value 
AND T1.employee = T2.employee
AND T1.entry_date = T2.entry_date); 

OR

DELETE FROM my_table AS T1 
WHERE EXISTS
(SELECT employee FROM my_table AS T2
WHERE T1.employee = T2.employee
AND T1.entry_date = T2.entry_date
GROUP BY T2.employee , T2.entry_date 
HAVING COUNT(*) = 1 );

Answer 3

As you are using Firebird 2.5 you can try this way:

DELETE FROM my_table
WHERE (employee, entry_date) IN (
    SELECT employee, entry_date
    FROM my_table
    GROUP BY employee, entry_date
    HAVING COUNT(*) = 1
);

This should delete all entries where an employee has only one entry per day.