mysql on duplicate key update the entire row

Question

this is a jsp code

 PreparedStatement pstmt =con.prepareStatement("select ssn,name,gender,bod,address,profession,phone from contacts ");
 ResultSet rss = pstmt.executeQuery();
 while(rss.next()){
 stmt = conn.createStatement();
String sql1 = "INSERT INTO contacts (id,ssn,name,gender,dob,address,profession,phone) VALUES (default,'" +rss.getString(1) + "','"+rss.getString(2) + "','"+rss.getString(3)+"','" +rss.getString(4) + "','"+rss.getString(5) +"','"+rss.getString(6)+"','"+rss.getString(7)+"' on duplicate key update ssn=VALUES("+rss.getString(1)+")";
 stmt.executeUpdate(sql1);
 out.println("<tr style='background-color:white'>");
 out.println("<td>"); out.println(rss.getString(1));out.println("</td>");
 out.println("<td>"); out.println(rss.getString(2));out.println("</td>");
 out.println("<td>"); out.println(rss.getString(3));out.println("</td>");
 out.println("<td>"); out.println(rss.getString(4));out.println("</td>");
 out.println("<td>"); out.println(rss.getString(5));out.println("</td>");
 out.println("<td>"); out.println(rss.getString(6));out.println("</td>");
 out.println("<td>"); out.println(rss.getString(7));out.println("</td>"); 
out.println("</tr>");
 }

---

out put:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on duplicate key update ssn=VALUES(1)' at line 1

note: Server version: 5.7.10-log MySQL Community Server

Answer 1

You used:

INSERT INTO contacts 
 (id, ssn, name, gender, dob, address, profession, phone) 
VALUES 
 (default, 1, 2, 3, 4, 5, 6, 7) 
ON DUPLICATE KEY UPDATE
 ssn = VALUES(1) ;

where 1, 2, 3, .. are the values passed form the application (check your method by the way, seems like it's vulnerable to SQL injection)

The error you get is because VALUES(1) is not valid syntax. The last line should be either:

 ssn = 1 ; -- the value , repeated

or:

 ssn = VALUES(ssn) ; -- reference to the value that was 
 -- to be inserted to column "ssn" 

• Rick James: Simplify that by removing id, and default,.

Answer 2

Whenever you have a query inside a loop, think about whether you can do the looping in SQL with a single query; that is, not really a loop.

INSERT INTO contacts 
 (ssn, name, gender, dob, address, profession, phone) 
 ON DUPLICATE KEY UPDATE
 ssn = VALUES(ssn),
 name = VALUES(name),
 ...
 SELECT ssn, name, gender, dob, address, profession, phone
 FROM ...

or, simply

INSERT IGNORE INTO contacts (ssn, ...)
 SELECT ssn, ... FROM ...

But... This does not make sense. You are reading all the rows of contacts and inserting them back into the same table? What did you mean to do?

I am assuming that ssn is a UNIQUE key; otherwise IODKU may not work.

Note that id does not need to be specified in any of the variants; assuming it is AUTO_INCREMENT and PRIMARY KEY. However, why have id? Just use SSN as the PK.

You need some serious security consulting -- If your machine is hacked into, the SSNs could expose you to serious legal consequences.