Skip to Main Content


Small typos can have big consequences...

Recently I managed to delete every row from a table, while I really only wanted to delete a subset.
What I did was something like this:
This will delete all 14 rows from emp, while I meant to delete just a few rows. Of course this is a silly example for emp/dept, but it illustrates the point.
The reason is obvious: dept does not contain a column named ename, so "select ename from dept" will always return all rows (a bit like "select 1 from dual").

How to avoid this? Easy: either never make a silly mistake like this (easier said than done) OR always use table aliases (or the complete table name). Same goes for views of course.

Some examples: Rowcount: 14 - while you aimed for a subset of those 14.

Rowcount: 14 - if you write it like this, the error becomes a bit more obvious.

Rowcount: 0 - you probably meant to do something like this.

ORA-00904 "dept.ename: invalid identifier" - using table names gives you the exception you need here.

ORA-00904 "d.ename: invalid identifier" - same goes for table aliases, which are a bit nicer to read.

Related tip: always make lots of backups, one day you'll be glad to have them. I sure was :-)