DBMS: Remove Duplicate Entries from Database Table in Oracel / MS SQl

Delete Duplicate rows from database tables

Method 1.

This is simple method to remove duplicate entries from database tables. You can use it in oracle as well as MS SQL. Duplicate entries in aspect of one column or more than one column. Here in this query 'id' can be primary key or any other unique data, which identifies each row uniquely.

Note : I tested this query it works good, its very simple and easy way to delete duplicate rows from tables.

DELETE
FROM TableName
WHERE id NOT IN
( SELECT MAX(id)
FROM TableName
GROUP BY DuplicateColumName1, DuplicateColumName2)

Method 2.

This is Another simple method to remove duplicate entries from database tables. Works well with both Oracle and MS SQL.

Step 1: Move the non duplicates (unique rows) into a temporary table

Oracle Method to move non duplicates rows into new table from old table

CREATE TABLE NewTable AS
SELECT * FROM OldTable
WHERE 1
GROUP BY DuplicateColumName1, DuplicateColumName2

MS SQL Method to move non duplicates rows into new table from old table

SELECT * INTO NewTable
FROM OldTable
WHERE 1
GROUP BY DuplicateColumName1, DuplicateColumName2


Step 2: Delete old table.

We no longer need the table with all the duplicate entries, so drop it!

DROP TABLE OldTable

Step 3: Rename the New Table to the name of the Old Table

Oracle Method to Rename tables

RENAME TABLE NewTable TO OldTable

MS SQL Method to Rename tables

ALTER TABLE OldTable RENAME TO NewTABLE

Java IO: Simple Java Program To Write Text File

Here is the simple java code to write text file.You need to import java.io package...
Used File, FileWriter & BufferedWriter. Simply copy paste the below code and run the code.




/* here is the complete code for WriteFile.java */


import java.io.*;

public class WriteFile
{
public static void main(String[] args)
{
//name of the file, it takes default path ie. current directory
String TextFileToWrite = "MyFile.txt";

try
{
File file=new File(TextFileToWrite);
FileWriter fw =new FileWriter(file);
BufferedWriter bw = new BufferedWriter(fw);

for(int line = 0 ; line <= 10 ; line ++)
{
bw.write("this is line " + line );
bw.newLine();
bw.flush();//flushing
}

bw.close();//closeing BufferedWriter

System.out.println("File Written sucessfully!!!");
}
catch (IOException ex)
{
ex.printStackTrace();
}
}
}

Javascript : Dynamic Dragable and Popup Div

Simple Dragable Div

<HTML>
<HEAD>
<TITLE>Dragable Div</TITLE>
<script type="text/javascript">

function agent(v)
{
return(Math.max(navigator.userAgent.toLowerCase().indexOf(v),0));
}
function xy(e,v)
{
return(v?(agent('msie')?event.clientY+document.body.scrollTop:e.pageY):(agent('msie')?event.clientX+document.body.scrollTop:e.pageX));
}

function dragOBJ(d,e)
{

function drag(e)
{
if(!stop)
{
d.style.top=(tX=xy(e,1)+oY-eY+'px');
d.style.left=(tY=xy(e)+oX-eX+'px');
}
}

var oX=parseInt(d.style.left),oY=parseInt(d.style.top),eX=xy(e),eY=xy(e,1),tX,tY,stop;

document.onmousemove=drag;
document.onmouseup=function()
{
stop=1;
document.onmousemove='';
document.onmouseup='';
};
}
</script>
</HEAD>

<BODY>

<div style="position: fixed; width: 500px; top: 150px; left: 250px; cursor: move;" onmousedown="dragOBJ(this,event); return false;">
<TABLE border = "1" width="30%">
<TR>
<TD colspan="2">This is Dragable Div</TD>
</TR>
<TR>
<TD>A</TD>
<TD>B</TD>
</TR>
<TR>
<TD>C</TD>
<TD>D</TD>
</TR>
<TR>
<TD>E</TD>
<TD>F</TD>
</TR>
</div>

</BODY>
</HTML>

Simple Popup Dragable Div

<HTML>
<HEAD>
<TITLE>Popup Dragable Div</TITLE>

<style>
/*intially hide div, show only on click*/
.parentDisable
{
z-index: 999;
width: auto;
height: auto;
position: absolute;
top: 0;
left: 0;
display: none;
background-color: #fff;
color: #aaa;
}
</style>

<script type="text/javascript">

//used for draging div
function agent(v)
{
return(Math.max(navigator.userAgent.toLowerCase().indexOf(v),0));
}
function xy(e,v)
{
return(v?(agent('msie')?event.clientY+document.body.scrollTop:e.pageY):(agent('msie')?event.clientX+document.body.scrollTop:e.pageX));
}

function dragOBJ(d,e)
{

function drag(e)
{
if(!stop)
{
d.style.top=(tX=xy(e,1)+oY-eY+'px');
d.style.left=(tY=xy(e)+oX-eX+'px');
}
}

var oX=parseInt(d.style.left),oY=parseInt(d.style.top),eX=xy(e),eY=xy(e,1),tX,tY,stop;

document.onmousemove=drag;
document.onmouseup=function()
{
stop=1;
document.onmousemove='';
document.onmouseup='';
};
}

//used for pop-up div
function pop(div)
{
document.getElementById(div).style.display='block';
return false
}
function hide(div)
{
document.getElementById(div).style.display='none';
return false
}
</script>
</HEAD>

<BODY>

<a href='#' onClick="return pop('myDiv')">Click Here</a> to popup div

<div style="position: fixed; width: 500px; top: 150px; left: 250px; cursor: move;" onmousedown="dragOBJ(this,event); return false;">
<div id='myDiv' class='parentDisable'>
<TABLE border = "1" width="30%">
<TR>
<TD colspan="2" align="right"><a href="#" onClick="return hide('myDiv')">Close</a></TD>
</TR>
<TR>
<TD>A</TD>
<TD>B</TD>
</TR>
<TR>
<TD>C</TD>
<TD>D</TD>
</TR>
<TR>
<TD>E</TD>
<TD>F</TD>
</TR>
</div>
</div>

</BODY>
</HTML>


Note : I tested this code with Mozilla FireFox, Google Chrome, Safari and Opera its working fine. But its not working with IE 8 and its higher version. Hopefully will come up with solution soon.

Java : JDBC Connection codes in Oracle and MS SQL

Oracle Connection

/* Here is complete code for OracleConnection.java */

import java.sql.*;

public class OracleConnection
{
public static void main(String[] args)
{
//connection
Connection con=null;
ResultSet rs=null;
Statement stmt=null;

try
{
Class.forName("oracle.jdbc.driver.OracleDriver"); //Step 1: Loading Drivers

con = DriverManager.getConnection("jdbc:oracle:thin:@10.10.10.10:8080:vas","username","password"); //Step 2: Making Connection

stmt = con.createStatement(); //Step 3: Creating JDBC Statement

String query = "Select * from EMPLOYEE";

rs = stmt.executeQuery(query); //Step 4: Execute the Ststement

while (rs.next()) //Step 5: Looping through the ResultSet
{
System.out.println(rs.getString(1)+""+rs.getString(2));
}

stmt.close(); //step 6: Close the Connection and Statement
con.close();
rs=null;
stmt=null;
con=null;
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}

MS SQL Connection

/* Here is complete code for MSSQLConnection.java */

import java.sql.*;

public class MSSQLConnection
{
public static void main(String[] args)
{
//connection
Connection con=null;
ResultSet rs=null;
Statement stmt=null;

try
{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); //Step 1: Loading Drivers

con = DriverManager.getConnection("jdbc:sqlserver://10.10.10.10:8080;databaseName=DBNAME;user=scott;password=tiger"); //Step 2: Making Connection

stmt = con.createStatement(); //Step 3: Creating JDBC Statement

String query = "Select * from EMPLOYEE";

rs = stmt.executeQuery(query); //Step 4: Execute the Ststement

while (rs.next()) //Step 5: Looping through the ResultSet
{
System.out.println(rs.getString(1)+""+rs.getString(2));
}

stmt.close(); //step 6: Close the Connection and Statement
con.close();
rs=null;
stmt=null;
con=null;
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}

DBMS : Simple method to create duplicate tables in Oracle and MS SQL

Oracle

1. Creating duplicate table with data in Oracle

Here New Table is exact replica of old table with same columns, same datatype with same data

create table new_table as
select * from old_table

2. Creating duplicate table with no data in oracle, only structure is copied

This is simple method to create New blank Table with same structure as old table. Just provide a false condition in where clause which will never be true

create table new_table as
select * from old_table
where ( any_column = 'false condition' )

MS SQL

1. Creating duplicate table with data in MS SQL

Similar to oracle here also new table is exact replica of old table with all data.

select * into new_table
from old_table

2. Creating duplicate table structure in MS SQL

Here also only table structure is copied with no data, just provide a false condition in where clause which will never be true.

select * into new_table
from old_table
where ( any_column = 'false_condition' )