Compare Two DataBase

[ComputerTechie]

Hello fellow Experts,

i have two database with the following tables
customers and scrub.

customers table has folowing:
id customers
Phone
First Name
Last name
Address
City
State
Zipcode
Email
Investment
Source
Assign
Statue
CustomerNotes

Scrub has the following
idscrub
First
Last

What i am trying to do is compare the scrub Database with the customer Database and find all the matchs to delete them out of customer database.

So far my code work for first record in customer them stops. I been changed code and still get the same results.

if there an easier way please let me know.

i have 3355 names to take out of the 77005 customer database.

I working with jsp and mysql.

Thanks for your time,

CT

<%@ page import="java.sql.*"%>
<%@ page import="javax.servlet.*"%>


<%
String connectionURL = "jdbc:mysql://localhost:3306/bjc?user=;password=";
Connection connection = null;
Connection Mconnection = null;
Statement statement = null;
Statement Mstatement = null;
ResultSet rs = null;
ResultSet Mrs = null;


Class.forName("com.mysql.jdbc.Driver").newInstance();
Mconnection = DriverManager.getConnection(connectionURL, "root", "password");
connection = DriverManager.getConnection(connectionURL, "root", "password");
Mstatement = Mconnection.createStatement();
statement = connection.createStatement();
Mrs = Mstatement.executeQuery("SELECT * FROM scrub");
rs = rs = statement.executeQuery("SELECT * FROM customers");

String holder1 = null;
String holder1a = null;
String holder2 = null;
String holder2a = null;
int idholder = 0;
int temp = 0;

while (rs.next()){
    idholder = rs.getInt("idcustomer");
    
 while (Mrs.next()){
     holder1 = Mrs.getString("last");
     holder2 = rs.getString("Last Name");
     holder1a = Mrs.getString("First");
     holder2a = rs.getString("First Name");
     
     out.println(idholder);
     out.println(" ");
     out.println(temp);
     out.println(" ");
     out.println(holder1a);
     out.println(" ");
     out.println(holder1);
     out.println(" ");
     out.println(holder2a);
     out.println(" ");
     out.println(holder2);
     out.println("<br>");
     
     
     if (holder2.equalsIgnoreCase(holder1) && holder2a.equalsIgnoreCase(holder2)){
         out.println("row "+idholder+" "+holder2a+" "+holder2+"\n");
     } 
     } temp++;  
}




rs.close();
statement.close();
connection.close();


%>

[C CSR]

Why does it look like you're comparing holder2a to holder2? Shouldn't it be holder2 = holder1, holder2a = holder1a?

[ComputerTechie]

Sorry i did post the older code here is new line

if (holder2.equalsIgnoreCase(holder1) && holder2a.equalsIgnoreCase(holder1a))

as for the compare you can not as for are i know use = and have to use .equals

if (holder2.equalsIgnoreCase(holder1) && holder2a.equalsIgnoreCase(holder2)){

CT

[C CSR]

I used "=" just as shorthand. If the New code still isn't working I'm out of my league on this. I was just looking at random questions and I noticed the 2a = 2 discrepancy. Hang in there:)

[lgm001]

God you people make your lives difficult...

Reads your mySql manual...

Using SQL outer joins will yield the differences.

SELECT * FROM customer LEFT JOIN scrub
-> ON customer.firstn ame = scrub.firstname
-> AND customer.lastna me = scrub.last
-> WHERE customer.firstn ame IS NULL;

Basically this says give me all the records that are in scrub and match them with customer... Make an empty customer.name field if there isn't a match in the customer... All you will get from this select is whats left in scrub.

[ComputerTechie]

lgm001,

You need to test what you wrote. it does not work but i did come up with a solustion that i will post. soon.

CT

[lgm001]

Well... post a dataset to work with then...

[ComputerTechie]

I cam up with the following solution.

SELECT customers.`First Name`,customers.`Last Name`,customers.idcustomer,scrub.idscrub,scrub.`First`,scrub.Last
FROM customers ,scrub
WHERE customers.`First Name` = scrub.`First` AND customers.`Last Name` = scrub.Last
ORDER BY customers.idcustomer  



DELETE FROM customers c
WHERE EXISTS (SELECT 'X' FROM scrub
WHERE c.`First Name` = scrub.`First` AND c.`Last Name` = scrub.Last
ORDER BY c.idcustomer )