Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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
add comment

2 Answers

up vote 5 down vote accepted

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.
share|improve this answer
add comment

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.

share|improve this answer
    
Comment - "in generally yes" answers the "is this bad practice" question in the original post ;) –  TomTom Sep 27 '13 at 6:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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