ORACLE

1 > Oracle SQL query for deleting duplicate row.

select * from employee;

Duplicate row

In above fetch, we have duplicate row for the employee id 1004 as show in circle.

delete from employee emp1 where rowid not in (select max(rowid) from employee emp2 where EMP1.EMPID = EMP2.EMPID)
or
delete from employee emp1 where rowid not in (select min(rowid) from employee emp2 where EMP1.EMPID = EMP2.EMPID)

After executing above query one row will  get delete.

select * from employee;

2> Transaction Control languages (TCL) Statements

Commit,

Rollback,

SAVEPOINT

and SET TRANSACTION

Commit: - COMMIT makes permanent any database changes made during the current transaction.

Rollback :- ROLLBACK ends the current transaction and undoes any changes made since the transaction began.

Savepoint :- A SAVEPOINT is a marker within a transaction that allows for a partial rollback. As changes are made in a transaction, we can create SAVEPOINTs to mark different points within the transaction. If we encounter an error, we can rollback to a SAVEPOINT or all the way back to the beginning of the transaction.

Example

    SQL> INSERT INTO AUTHOR

    2 VALUES ('A11l', 'john',

    3 'garmany', '123-345-4567',

    4 '1234 here st', 'denver',

    5 'CO','90204', '9999');

    1 row created.

    SQL> savepoint in_author;

    Savepoint created.

    SQL> INSERT INTO BOOK_AUTHOR VALUES ('A111', 'B130', .20);

    1 row created.

    SQL> savepoint in_book_author;

    Savepoint created.

    SQL> INSERT INTO BOOK

    2 VALUES ('B130', 'P002', 'easy oracle sql',

    3 'miscellaneous', 9.95, 1000, 15, 0, '',

    4 to_date ('02-20-2005','MM-DD-YYYY'));

    1 row created.

    SQL> rollback to in_author;

    Rollback complete.

set transaction :- SET TRANSACTION sets transaction properties such as read/write access and isolation level