This is a method of duplicating a row in an Oracle with a few specified column values to be changed/updated.

I needed to find an easy way to do this and could not find anything on the net so I made my own method of doing it. This is only really of any use if the table has a large number of columns in it, with most columns retaining their original value and a few column values changing. Otherwise it’s probably better to use INSERT INTO <table> SELECT FROM….

Basically, this method will replicate a row and apply updates to columns which are specified by you. Column updates are entered in as a key value pair in a  HashMap. Therefore you are not limited to the number of columns you can change/update. Obviously the primary key AS A MINIMUM will need to change else there will be a duplicate row error.

The key to getting this working is to store the row in a temporary table, modify the data slightly and then copy it back in.

Initial Setup

• An Oracle database is needed. Oracle Express 10g was used for this demo.

• JDBC drivers are also required (usually ojdb5.jar or ojdbc6.jar)

Creating Example database

Log in and create a schema/user. e.g. “replicate”

Create a table and insert some test data

[code lang=”SQL”]

create table employee (id INTEGER PRIMARY KEY, first_name varchar2(32),last_name varchar2(32), dob date,department varchar2(16),joined date,salary number(6))

insert into employee values (1,’bob’, ‘jones’,to_date(’16/06/1980′,’dd/mm/yyyy’),’finance’,sysdate-300,95000);
insert into employee values (2,’mary’, ‘smith’,to_date(’16/03/1966′,’dd/mm/yyyy’),’hr’,sysdate-600,95000);
insert into employee values (3,’steven’, ‘briggs’,to_date(’16/03/1946′,’dd/mm/yyyy’),’hr’,sysdate-1210,122000);
[/code]

After insertion, verify. Should look like this

The following code will insert a replicated row with department value changing for row 3.

Usage: new ReplicateRow().replicateQuery(table_query,tmpTableName, originalTableForFinalInsert, hm);

1=query

2=temporary table

3=table to re-insert row into (same table as specified in 1)

4=Map of columns and values to update

[code lang=”Java”]

package alphastar;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

public class ReplicateRow {

public Connection conn;

/**
* @param args
*/
public static void main(String[] args) {
new ReplicateRow().process();
}

public void getConnection() {
try {
Class.forName(“oracle.jdbc.driver.OracleDriver”);
String url = “jdbc:oracle:thin:@//192.168.0.104:1521/XE”;
conn = DriverManager.getConnection(url,”replicate”,”password”);
if (conn!=null) System.out.println(“have connection!”); else System.out.println(“don’t have connection”);
} catch (Exception e) {
e.printStackTrace();
}
}

public void process() {
getConnection();
String employee_query= “select e.* ”
+ “from employee e ”
+ “where ”
+ “1=1 ”
+ “and salary=122000 “;
String tmpTableName = “temptable”;
String originalTableForFinalInsert = “employee”;
Map<String,String> hm = new HashMap<String,String>();

// build up the columns to change
hm.put(“id”, “999”); // really should do this properly. ie get 1 + last value in DB
hm.put(“department”,”marketing”);

replicateQuery(employee_query,tmpTableName, originalTableForFinalInsert, hm);
}

private boolean replicateQuery(
String queryToTmpTable,
String tmpTableName,
String originalTableToReInsert,
Map columnChangesMap) {

int updateQuery = 0;

try {
PreparedStatement ps = conn.prepareStatement(queryToTmpTable);
ResultSet rs = ps.executeQuery();
Statement stmt = conn.createStatement();

boolean tableExists = checkTableExists(tmpTableName);
if (tableExists) {
String flushToEmpty = “delete from “+tmpTableName;
updateQuery = stmt.executeUpdate(flushToEmpty); // if hanging, commit in benthic
System.out.println(updateQuery+” table exists”);
} else {
String cs = copyStructure(queryToTmpTable, tmpTableName);
System.out.println(cs);
updateQuery = stmt.executeUpdate(cs);
System.out.println(updateQuery+” table doen’t exist. create it”);
}
// insert data to temp table
String insert=”INSERT into “+tmpTableName+” “+queryToTmpTable;
int res = stmt.executeUpdate(insert);
System.out.println(res+” done insertion to “+tmpTableName+”\n”);

doUpdateOfColsOnTempTable(tmpTableName, columnChangesMap);

String queryToTable = “select * from “+tmpTableName;
insertRowToOriginalTable(originalTableToReInsert, queryToTable); // e.g. txn_event,xyz_new

stmt.close();
conn.close();
return true;

} catch (SQLException ex) {
System.err.println(ex);
return false;
}

}

/**
*
* @param table
* @return true if the table exists. false otherwise
* @throws SQLException
*/
private boolean checkTableExists(String table) throws SQLException {
String tableUC = table.toUpperCase();
boolean status = false;

String checkExists = “”
+ “SELECT COUNT(1) ”
+ “FROM USER_TABLES ”
+ “WHERE TABLE_NAME = ‘”+tableUC+”‘ “; // must be UC
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(checkExists);
boolean rowFound = rs.next();
if (rowFound== false) status = false;
String countVal = rs.getString(1);

if (countVal.equals(“1”)) {
status = true;
} else if (countVal.contains(“0”)) {
status = false;
} else {
// shouldn’t get here
status = false;
}
return status;
}

private boolean doUpdateOfColsOnTempTable(String tableName, Map colsAndVals) throws SQLException {
Set entrySet = colsAndVals.entrySet();
Iterator i = entrySet.iterator();
Map.Entry me = null;
StringBuffer updateQuery = new StringBuffer();
updateQuery.append(“update “+tableName+” “);
int counter = 0;
while (i.hasNext()) {
me = (Map.Entry) i.next();
if (counter == 0) updateQuery.append(“set “);
if (counter >= 1) updateQuery.append(“,”);
updateQuery.append(me.getKey()+”='”+me.getValue()+”‘”);
System.out.println(“key = “+me.getKey());
System.out.println(“val = “+me.getValue()+”\n”);
counter++;
}
if (counter == 0) return false;

Statement stmt = conn.createStatement();
int res = stmt.executeUpdate(updateQuery.toString());
stmt.close();
System.out.println(res+” done the set statement with keyset”);
return true;
}

private String copyStructure(String in, String tmpTableName) {
// 1=0 will ensure that only the structure is copied. not data
String newTable = “create table “+tmpTableName+” as “+in+” and 1=0″;
return newTable;
}

/**
* This will insert the newly modified row back to the original table
*/
private boolean insertRowToOriginalTable(String origTable, String queryToTable) throws SQLException {
String insert = “INSERT INTO “+origTable+” “+queryToTable;
Statement stmt = conn.createStatement();
int res = stmt.executeUpdate(insert);
System.out.println(res+” done insert into Original table\n”);
if (res==1) return true;
else return false;
}

}

[/code]