Again, one of my students asked me a question during the Oracle 11g Introduction to SQL class.
"How would you delete only 1 record of 2 identical records, all with same column values and there is no PK (Primary Key) to uniquely identify them?"
I told her that we can make use of the ROWID pseudocolumn.T. Oracle Database rowid values contain information necessary to locate a row:
- They are the fastest way to access a single row.
- They can show you how the rows in a table are stored.
- They are unique identifiers for rows in a table.
To simulate the scenario, I created a sample table named Test:
SQL> CREATE TABLE TestThen, I inserted two identical records with no primary key
SQL> INSERT INTO Test
SQL> INSERT INTO TestThen, selected the rowid of two identical records
SQL> SELECT rowid, testThen, the query returned
Then, I tried to delete one of the two identical records
SQL> DELETE FROM TestThen, the result says 1 row deleted
WHERE rowid = 'AAASDcAAEEAADOMAAA';
Yey, we've successfully deleted one of the two identical records. ^_~