Results 1 to 3 of 3

Thread: Help needed for a sql query

  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Help needed for a sql query

    I am just posting a simple scenario of my problem which is typical then thsi actually.

    there are 2 tables PERSON & CUSTOMER

    PERSON

    FNAME LNAME SYNC
    A B N
    A C N
    D E N
    F G N
    H I N
    J K N

    CUSTOMER

    FNAME LNAME
    A B
    D E
    H I
    Now i have to update SYNC field in PERSON as Y for the records which exists in table customer, for eg these rescords exist in person table also
    FNAME LNAME
    A B
    D E
    H I

    i want to make SYNC field as Y for these records in PERSON table.

    I tried this way i wrote this query :

    SELECT FNAME FROM PRAGS.CUSTOMER WHERE (FNAME) IN (SELECT FNAME FROM PRAGS.PERSON)

    this will tell us if a particular FNAME exist in customer table or not, now to update the SYNC field in PERSON i tried using update query as

    UPDATE PRAGS.PERSON SET SYNC='Y' WHERE EXISTS(SELECT FNAME FROM PRAGS.CUSTOMER WHERE (FNAME) IN (SELECT FNAME FROM PRAGS.PERSON))

    this updates all the SYNC as Y because as asoon as SELECT FNAME FROM PRAGS.CUSTOMER WHERE (FNAME) IN (SELECT FNAME FROM PRAGS.PERSON) evalutaes to be truew it updates all sync as Y. i also tried and statemnet but it didn't work. please suggest me a way to work it out

    Also this is just a small example of what i want to do. Actual person table and customer table will contain more than 25 lac records. so how to do it optimally.

    i want to do it using SQL query.

    i have also done this using a java program below, this solved my program but i dont know how to do it optimally because recordset can not hold 25 lac records , thsi is not viable.

    String str = "SELECT FNAME FROM PRAGS.PERSON";

    rs = stmt.executeQuery(str);

    while(rs.next()){
    String cfName="";
    String fName = rs.getString(1);
    //String lName = rs.getString(2);

    //rs1 = stmt.executeQuery("SELECT FNAME, LNAME FROM PRAGS.CUSTOMER WHERE (FNAME, LNAME) IN ("+fName+","+lName+")");
    rs1 = stmt1.executeQuery("SELECT FNAME FROM PRAGS.CUSTOMER WHERE (FNAME) IN ('"+fName+"')");
    while(rs1.next()){
    cfName = rs1.getString(1);
    }

    if(!cfName.equals("")){
    stmt1.executeUpdate("UPDATE PRAGS.PERSON SET SYNC = 'Y' WHERE FNAME ='"+fName+"'");
    }

    try {
    if(rs1!=null){
    rs1.close();
    }
    } catch (SQLException e) {
    System.out.println("Exception is "+e.getMessage());

    }

    }

    please help
    thanks.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Which rdbms?

  3. #3
    Join Date
    Apr 2009
    Posts
    86
    cprash.aggarwal, since you don't mention what DBMS or version you are using, I don't know if this will work for you or not. It does work in the DBMS I am familiar with:

    UPDATE PRAGS.PERSON P
    SET SYNC = 'Y'
    WHERE EXISTS (SELECT *
    FROM PRAGS.CUSTOMER C
    WHERE A.FNAME = C.FNAME
    AND A.LNAME = C.LNAME)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •