How will you delete duplicating rows from a base table?

How will you delete duplicating rows from a base table?
by

1 Answer

Answer :

delete from table_name where rowid not in (select max(rowid) from
table group by duplicate_values_field_name); or delete
duplicate_values_field_name dv from table_name ta where rowid
<(select min(rowid) from table_name tb where ta.dv=tb.dv);
by

Related questions

How you will avoid duplicating records in a query?

Description : How you will avoid duplicating records in a query?

Last Answer : By using DISTINCT

Deleting the Duplicate rows in the table

Description : Deleting the Duplicate rows in the table

Last Answer : We can delete the duplicate rows in the table by using the Rowid

How do you find the numbert of rows in a Table ?

Description : How do you find the numbert of rows in a Table ?

Last Answer : A bad answer is count them (SELECT COUNT(*) FROM table_name) A good answer is :- 'By generating SQL to ANALYZE TABLE table_name COUNT STATISTICS by querying Oracle System Catalogues (e.g ... utility which Oracle released which makes it unnecessary to do ANALYZE TABLE for each Table individually.

A table is having few rows, should you create indexes on this table

Description : A table is having few rows, should you create indexes on this table

Last Answer : Small tables do not require indexes; if a query is taking too long, then the table might have grown from small to large. You can create an index on any column; however, if the ... , creating an index on the column does not increase performance and the index takes up resources unnecessarily.

Compare and contrast TRUNCATE and DELETE for a table.

Description : Compare and contrast TRUNCATE and DELETE for a table.

Last Answer : Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just ... other hand, is a DML operation, which will produce a rollback and thus take longer to complete.

Which can be used to delete all the rows if a table? A) Delete * from table_name B) Delete from table_name C) Delete table_name D) all rows cannot be deleted at a time.

Description : Which can be used to delete all the rows if a table? A) Delete * from table_name B) Delete from table_name C) Delete table_name D) all rows cannot be deleted at a time.

Last Answer : A) Delete * from table_name

If an unique key constraint on DATE column is created, will it validate the rows that are inserted with SYSDATE ?

Description : If an unique key constraint on DATE column is created, will it validate the rows that are inserted with SYSDATE ?

Last Answer : It won't, Because SYSDATE format contains time attached with it.

What is the built-in routine used to count the no of rows in a group?

Description : What is the built-in routine used to count the no of rows in a group?

Last Answer : Get_group _row_count System Variables

What is the use of ROWS option in IMP command ?

Description : What is the use of ROWS option in IMP command ?

Last Answer : A flag to indicate whether rows should be imported. If this is set to 'N' then only DDL for database objects will be executed.

What is the use of ROWS option in EXP command ?

Description : What is the use of ROWS option in EXP command ?

Last Answer : Flag to indicate whether export should compress fragmented segments into single extents.

Which parameter in Storage clause will reduce no. of rows per block?

Description : Which parameter in Storage clause will reduce no. of rows per block?

Last Answer : PCTFREE parameter Row size also reduces no of rows per block.

What are built-ins used for Processing rows?

Description : What are built-ins used for Processing rows?

Last Answer : GET_GROUP_ROW_COUNT(function) GET_GROUP_SELECTION_COUNT(function) GET_GROUP_SELECTION(function) RESET_GROUP_SELECTION(procedure) SET_GROUP_SELECTION(procedure) UNSET_GROUP_SELECTION(procedure)

What are the built-ins used for processing rows?

Description : What are the built-ins used for processing rows?

Last Answer : Get_group_row_count(function) Get_group_selection_count(function) Get_group_selection(function) Reset_group_selection(procedure) Set_group_selection(procedure) Unset_group_selection(procedure)

If a View on a single base table is manipulated will the changes be reflected on the base table ?

Description : If a View on a single base table is manipulated will the changes be reflected on the base table ?

Last Answer : If changes are made to the tables which are base tables of a view will the changes be reference on the view.

Committed block sometimes refer to a BASE TABLE ?

Description : Committed block sometimes refer to a BASE TABLE ?

Last Answer : False

What is difference between TRUNCATE & DELETE ?

Description : What is difference between TRUNCATE & DELETE ?

Last Answer : TRUNCATE commits after deleting entire table i.e., can not be rolled back. Database triggers do not fire on TRUNCATEDELETE allows the filtered deletion. Deleted records can be rolled back or committed.Database triggers fire on DELETE.

What is ON DELETE CASCADE ?

Description : What is ON DELETE CASCADE ?

Last Answer : When ON DELETE CASCADE is specified ORACLE maintains referential integrity by automatically removing dependent foreign key values if a referenced primary or unique key value is removed.

Differentiate between TRUNCATE and DELETE?

Description : Differentiate between TRUNCATE and DELETE?

Last Answer : TRUNCATE deletes much faster than DELETE TRUNCATE DELETE It is a DDL statement It is a DML statement It is a one way trip,cannot ROLLBACK One can Rollback Doesn't have selective features (where clause) Has Doesn't fire database triggers Does It requires disabling of referential constraints.

What is the usage of an ON-INSERT,ON-DELETE and ON- UPDATE TRIGGERS ? These triggers are execu

Description : What is the usage of an ON-INSERT,ON-DELETE and ON-UPDATE TRIGGERS ? These triggers are execu

Last Answer : These triggers are executes when inserting, deleting and updating operations are performed and can be used to change the default function of insert, delete or update respectively. For Eg, instead of inserting a row in a table an existing row can be updated in the same table.

What are the different types of Delete details we can establish in Master-Details?

Description : What are the different types of Delete details we can establish in Master-Details?

Last Answer : Cascade Isolate Non-isolate

What are the different types of Delete details we can establish in Master-Details?

Description : What are the different types of Delete details we can establish in Master-Details?

Last Answer : Content canvas views Stacked canvas views Horizontal toolbar vertical toolbar.

What is the difference between DELETE and TRUNCATE statements?

Description : What is the difference between DELETE and TRUNCATE statements?

Last Answer : The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation ... be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

Which one is faster: DELETE or TRUNCATE?

Description : Which one is faster: DELETE or TRUNCATE?

Last Answer : TRUNCATE

……….. can decrease the space on hard disks by duplicating files. -Technology

Description : ……….. can decrease the space on hard disks by duplicating files. -Technology

Last Answer : Virusescan decrease the space on hard disks by duplicating files.

Assertion: Coacervates are believed to be precursors of life. Reason: Coacervates were self duplicating aggregates of proteins surrounded by lipid mol

Description : Assertion: Coacervates are believed to be precursors of life. Reason: Coacervates were self duplicating aggregates of proteins surrounded by lipid mol

Last Answer : Assertion: Coacervates are believed to be precursors of life. Reason: Coacervates were self duplicating ... both the assertion and reason are false.

Should he focus on duplicating his successful offline marketing efforts to an online audienceTRUE?

Description : Should he focus on duplicating his successful offline marketing efforts to an online audienceTRUE?

Last Answer : 1

If He charges 17.95 for each CD and his expenses include 1250 for duplicating equipment and 0.65 for each blank CD which equation shows the profit (p) for selling n CD's?

Description : If He charges 17.95 for each CD and his expenses include 1250 for duplicating equipment and 0.65 for each blank CD which equation shows the profit (p) for selling n CD's?

Last Answer : What is the answer ?

EPROM can be used for A) Erasing the contents of ROM B) Reconstructing the contents of ROM C) Erasing and reconstructing the contents of ROM D) Duplicating ROM

Description : EPROM can be used for A) Erasing the contents of ROM B) Reconstructing the contents of ROM C) Erasing and reconstructing the contents of ROM D) Duplicating ROM

Last Answer : Answer : C

Which of the following is not a way of acquiring software? a) Buying pre-written software b) Ordering customized software c) Downloading public-domain Software d) Duplicating the software

Description : Which of the following is not a way of acquiring software? a) Buying pre-written software b) Ordering customized software c) Downloading public-domain Software d) Duplicating the software

Last Answer : Answer: d Explanation: Duplication is not a correct way of acquiring the software. All the other options are valid. Apart from these, another option could be development of a customized software

In an office, Franking machine is used for– (A) Punching card (B) Accounting work (C) Duplicating (D) Postal stamp fixing

Description : In an office, Franking machine is used for– (A) Punching card (B) Accounting work (C) Duplicating (D) Postal stamp fixing

Last Answer : Answer: Postal stamp fixing

EPROM can be used for --- 1) Erasing the contents of ROM 2) Reconstructing the contents of ROM 3) Erasing and reconstructing the contents of ROM 4) Duplicating ROM

Description : EPROM can be used for --- 1) Erasing the contents of ROM 2) Reconstructing the contents of ROM 3) Erasing and reconstructing the contents of ROM 4) Duplicating ROM

Last Answer : 3) Erasing and reconstructing the contents of ROM

In planning and construction of a cast metal partial denture the study cast, M07 A. facilitate the construction of custom/special trays B. minimize the need for articulating C. provide only limited information about inter ridge distance, which is best assessed clinically D. can be used as a working cast when duplicating facilities are not available

Description : In planning and construction of a cast metal partial denture the study cast, M07 A. facilitate the construction of custom/special trays B. minimize the need for articulating C. provide only ... best assessed clinically D. can be used as a working cast when duplicating facilities are not available

Last Answer : A. facilitate the construction of custom/special trays

Which of the following statements is FALSE about weak entity set ? (A) Weak entities can be deleted automatically when their strong entity is deleted. (B) Weak entity set avoids the data duplication and consequent possible inconsistencies caused by duplicating the key of the strong entity. (C) A weak entity set has no primary keys unless attributes of the strong entity set on which it depends are included. (D) Tuples in a weak entity set are not partitioned according to their relationship with tuples in a strong entity set.

Description : Which of the following statements is FALSE about weak entity set ? (A) Weak entities can be deleted automatically when their strong entity is deleted. (B) Weak entity set avoids the data ... weak entity set are not partitioned according to their relationship with tuples in a strong entity set.

Last Answer : (D) Tuples in a weak entity set are not partitioned according to their relationship with tuples in a strong entity set.

Can a repeating frame be created without a data group as a base?

Description : Can a repeating frame be created without a data group as a base?

Last Answer : No

For a field in a repeating frame, can the source come from the column which does not exist in the data group which forms the base for the frame?

Description : For a field in a repeating frame, can the source come from the column which does not exist in the data group which forms the base for the frame?

Last Answer : Yes

Atleast how many set of data must a data model have before a data model can be base on it?

Description : Atleast how many set of data must a data model have before a data model can be base on it?

Last Answer : Four

What are the basic element of Base configuration of an oracle Database ?

Description : What are the basic element of Base configuration of an oracle Database ?

Last Answer : It consists of one or more data files. one or more control files. two or more redo log files. The Database contains multiple users/schemas one or more rollback segments one or more tablespaces ... (Data Base Write) ARCH (ARCHiver) CKPT (Check Point) RECO Dispatcher User Process with associated PGS

Can one export a subset of a table? (for DBA

Description : Can one export a subset of a table? (for DBA

Last Answer : From Oracle8i one can use the QUERY= export parameter to selectively unload a subset of the data from a table. Look at this example: exp scott/tiger tables=emp query=\"where deptno=10\"

How can you gather statistics on a table?

Description : How can you gather statistics on a table?

Last Answer : The ANALYZE command.

How would you determine who has added a row to a table?

Description : How would you determine who has added a row to a table?

Last Answer : Turn on fine grain auditing for the table.

Explain the use of table functions.

Description : Explain the use of table functions.

Last Answer : Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement. They are also used to pipeline information in an ETL process.

A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?

Description : A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?

Last Answer : Disable the foreign key constraint to the parent, drop the table, re- create the table, enable the foreign key constraint.

What type of index should you use on a fact table?

Description : What type of index should you use on a fact table?

Last Answer : A Bitmap index.

Give two examples of how you might determine the structure of the table DEPT.

Description : Give two examples of how you might determine the structure of the table DEPT.

Last Answer : Use the describe command or use the dbms_metadata.get_ddl package.

How many columns can table have?

Description : How many columns can table have?

Last Answer : The number of columns in a table can range from 1 to 254.

Table Space,Data Files,Parameter File, Control Files

Description : Table Space,Data Files,Parameter File, Control Files

Last Answer : Table Space :: The table space is useful for storing the data in the database.When a database is created two table spaces are created. a) System Table space :: This data file stores all the tables ... They contain the Db name, name and location of dbs, data files ,redo log files and time stamp.

Suppose a customer table is having different columns like customer no, payments.What will be the query to select top three max payments?

Description : Suppose a customer table is having different columns like customer no, payments.What will be the query to select top three max payments?

Last Answer : SELECT customer_no, payments from customer C1 WHERE 3

Find out nth highest salary from emp table?

Description : Find out nth highest salary from emp table?

Last Answer : SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal

maxvalue.sql Select the Nth Highest value from a table?

Description : maxvalue.sql Select the Nth Highest value from a table?

Last Answer : select level, max('col_name') from my_table where level = '&n' connect by prior ('col_name') > 'col_name') group by level; Example: Given a table called emp with the following columns: -- id number -- name ... , max(sal) from emp -- where level=2 -- connect by prior sal > sal -- group by level

What are the data types allowed in a table ?

Description : What are the data types allowed in a table ?

Last Answer : CHAR,VARCHAR2,NUMBER,DATE,RAW,LONG and LONG RAW.