2

I've came across the following SQL/PLSQL while investigating a stored procedure written by someone else:

1. V_COUNT NUMBER(5,0);
.........
2. SELECT COUNT(*) INTO V_COUNT FROM TABLE1 WHERE COL1='SOMEVALUE';
3. IF V_COUNT>0 THEN
4.   DELETE FROM TABLE1 WHERE COL1='SOMEVALUE';
5. END IF;

Isn't only the DELETE statement in the line 4 sufficient to cover all the logic that's meant with these 5 lines? I first thought maybe V_COUNT is used somewhere else, but it's not. I can't think of any good reason in making a simple delete statement so complex. Do you also think it's a bad design or is there a point that I can't see?

  • 1
    Note that FROM is optional in Oracle. And if you name your tables in the plural, then that reads very well: DELETE widgets WHERE status = 'obsolete'; – Colin 't Hart Sep 26 '13 at 10:14
  • Wouldn't this practice miss DML statements that change the data between 2nd and 3rd statements? – vonPryz Sep 26 '13 at 17:58
7

I would just delete.

Even if I had to only delete if there were say exactly 2 records, then I would add this condition to the where clause, like

DELETE FROM table1
WHERE col1='somevalue'
AND (SELECT COUNT(*) FROM table1 WHERE col1 = 'somevalue') = 2;

In all cases when writing a delete statement consider that in general the where clause is usually either

  • restricting to a single row using a key;
  • restricting to a set of rows using a flag/code/status column; or
  • restricting to a set of rows using a range of values -- often dates.
1

In generally - yes. THere is nothing to be gained by doing it like you do - not with this logic. Contrary - the example does 2 selects (to find the rows to be deleted) on the table, so it is slower.

  • Comment - "in generally yes" answers the "is this bad practice" question in the original post ;) – TomTom Sep 27 '13 at 6:11
  • I strongly disagree with this. Don't make the database do extra work. Besides, what's to say that the count won't be different a split second after doing it. So just delete. – Colin 't Hart Jan 24 '19 at 10:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.