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.