Compare Two DataBase

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • ComputerTechie
    New Member
    • Jan 2012
    • 6

    Compare Two DataBase

    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
    Code:
    <%@ 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
    New Member
    • Jan 2012
    • 144

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

    Comment

    • ComputerTechie
      New Member
      • Jan 2012
      • 6

      #3
      Sorry i did post the older code here is new line

      Code:
      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

      Code:
      if (holder2.equalsIgnoreCase(holder1) && holder2a.equalsIgnoreCase(holder2)){
      CT

      Comment

      • C CSR
        New Member
        • Jan 2012
        • 144

        #4
        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:)

        Comment

        • lgm001
          New Member
          • Jan 2012
          • 9

          #5
          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.

          Comment

          • ComputerTechie
            New Member
            • Jan 2012
            • 6

            #6
            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

            Comment

            • lgm001
              New Member
              • Jan 2012
              • 9

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

              Comment

              • ComputerTechie
                New Member
                • Jan 2012
                • 6

                #8
                I cam up with the following solution.


                Code:
                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 )

                Comment

                Working...