Oracle PL/SQL Tips: Create Table Express

CREATE TABLE NEW_TABLE_NAME AS
SELECT * FROM EXISTING_TABLE_NAME WHERE 1=2;

The above statement will create an empty table which has the same structure as the existing table.

CREATE TABLE NEW_TABLE_NAME AS
SELECT COLUMN1, COLUMN2 FROM EXISTING_TABLE_NAME WHERE 1=2;

You can use the above statement to create an empty table with selected fields from the existing table.

Oracle PL/SQL Tips: Deleting Duplicate Rows

You can use one of the following queries to identify and/or remove duplicate rows from a table leaving only unique records in the table.

Method 1: Based on key field(s), delete all rows which have greater ROWIDs than the smallest ROWID. ROWID is a pseudocolumn that uniquely identifies a row within a table.

DELETE FROM table_name A
WHERE ROWID > ( SELECT min(ROWID)
              FROM table_name B
              WHERE A.key_field = B.key_field );

--delete all rows which have smaller ROWIDs than
--the biggest ROWID for given key values
DELETE FROM table_name A
WHERE ROWID > ( SELECT max(ROWID)
                FROM table_name B
                WHERE A.key_field = B.key_field );

Method 2: Almost similar to Method 1, but using group by key field(s) criteria to delete.

DELETE FROM my_table t1
WHERE ROWID NOT IN ( SELECT min(ROWID)
                 FROM my_table t2
                 GROUP BY key_field1, key_field2 );

The Exploding Growth of the Internet

Mount Point

Starting a new blog on the world of information technology (IT), from an analyst's perspective. Stay tuned for interesting entries on problems, challenges, rants and tips.