How would I delete duplicate data from two tables in Oracle SQL Developer -
my first table has columns id, invdate , invnumber values:
(1, 01/10/13, 1) (2, 02/10/13, 2) (2, 02/10/13, 2) (3, 03/10/13, 3) (4, 04/10/13, 4) (4, 05/10/13, 4)
my second table b has columns id, dateofbirth, lastname, firstname values:
(1, 19/06/1997, fox, migan) (2, 30/07/1993, todd, james) (2, 30/07/1993, todd, james) (3, 1/08/1994, xi, ji) (4, 2/07/1992, fared, bob) (4, 2/07/1992, fared, bob)
how use delete () function delete duplicated rows in both tables? think should merge tables somehow delete duplicated rows not sure.
as results,
select *
should give:
(1, 01/10/13, 1) (2, 02/10/13, 2) (3, 03/10/13, 3) (4, 04/10/13, 4) select * b;
should give:
(1, 19/06/1997, fox, migan) (2, 30/07/1993, todd, james) (3, 1/08/1994, xi, ji) (4, 2/07/1992, fared, bob)
update (solution) :
delete rowid>(select min(rowid) b a.id= b.id);
can explain sql me?
below example "b" table
taken here http://www.dba-oracle.com/t_delete_duplicate_table_rows.htm
(you can similar "a" table also)
delete b t1 t1.rowid > ( select t2.rowid b t2 t1.id = t2.id , t1.dateofbirth = t2.dateofbirth , t1.lastname = t2.lastname , t1.firstname = t2.firstname );
Comments
Post a Comment