May be I am not getting that clear. What is possible practically that SQL doesn't allow?

What is the volume of data that you foresee to subject to this operation?
How frequently this Update/Delete is to be scheduled or done?
Do you have proper indexes in place to handle this?

Answers to these will give you the answer for the performance hit that you are worried about.

_________________________
- Vijay G
Strive for Perfection

Pratically its possible to do the operations which i have mentioned in another thread in single step.

Assume the patient is being discharged from the hospital, then in that case I will have to do two things
1 Update the refcount (decrement by 1).
2 If RefCount becomes 0 that entry should be deleted from the table.

Pratically,
 Assume its a BTree, We can search the binary tree with the given name and if the name is found (in this case surely gonna happen), we reduce the associated refcount value (decrement by 1), now at that time itself we can check whether its value has become zero, if yes we can delete that node in O(1) time. Whereas in SQL, even though for updating i reach the given node in the Btree, but then for delete i will again have to reach the given node.

The solution which you gave (delete first, check rowcount, update refcount) also faces this problem, while deleting i will have to search the tree with refcount = 1 and given name, if the refcount is not zero i will again have to search the tree with the given name to update the value of refcount.

I hope this will clear u, what i am expecting.

Yes it is clear, but any DML is going to be an independant transaction, where in you want to do update and delete in one step, which is impossible with any database management system AFAIK.

_________________________
- Vijay G
Strive for Perfection

Thanks Vijay for that info that its impossible.

Regards,
Phani