使用Prepared Statement中的where子句进行SQL查询[重复]

This question already has an answer here:

这个问题在这里已有答案：

How to add a where clause in a MySQL Insert statement? 8 answers
如何在MySQL Insert语句中添加where子句？ 8个答案

I am working on a gate entry system, in which i am inserting in-time and out-time. While writing sql query for out-time, it is showing error at WHERE clause. I am not able to solve the error. What will be exact SQL query?

我正在研究一个门禁系统，我正在插入时间和时间。在为out-time编写sql查询时，它在WHERE子句中显示错误。我无法解决错误。什么是SQL查询？

java.util.Date date = new java.util.Date();
java.sql.Timestamp sqlTime = new java.sql.Timestamp(date.getTime());
PreparedStatement ps = con.prepareStatement("insert into ENTRY(OUTTIME) values(?,?,?,?) WHERE (ENTRY.ROLLNUMBER='"+rollno+"' AND ENTRY.OUTTIME ='NULL')");
ps.setTimestamp(4,sqlTime);
ps.executeUpdate();

5 个解决方案

#1

You need to UPDATE the existing row

您需要更新现有行

PreparedStatement ps = con.prepareStatement("UPDATE ENTRY SET OUTTIME =? WHERE ROLLNUMBER=?");
ps.setTimestamp(1,sqlTime);
ps.setString(2, rollno);

#2

This isn't how an insert statement works. If you want to update an existing record, you'd need an update statement:

这不是插入语句的工作方式。如果要更新现有记录，则需要更新语句：

PreparedStatement ps = 
    con.prepareStatement("UPDATE entry SET outtime = ? WHERE rollbumber = ?");
ps.setTimestamp(1, sqlTime);
ps.setInt(2, myRollNo);

#3

It makes no sense for an insert statement to have a WHERE clause which refers back to the same record. I propose the following code:

对于insert语句来说，使用WHERE子句引用相同的记录是没有意义的。我提出以下代码：

java.sql.Timestamp sqlTime = new java.sql.Timestamp(date.getTime());
String sql = "INSERT INTO ENTRY(OUTTIME) VALUES (?)";
PreparedStatement ps = con.prepareStatement(sql);
ps.setTimestamp(1, sqlTime);
ps.executeUpdate();

#4

Either you update or change your insert statement as below:

您可以更新或更改插入语句，如下所示：

"insert into ENTRY(OUTTIME) select col1 from ENTRY WHERE ENTRY.ROLLNUMBER='"+rollno+"' AND ENTRY.OUTTIME ='NULL'"

#5

Use this code

使用此代码

java.util.Date date = new java.util.Date();
java.sql.Timestamp sqlTime = new java.sql.Timestamp(date.getTime());
PreparedStatement ps = con.prepareStatement("update ENTRY set OUTTIME= ?  WHERE ENTRY.ROLLNUMBER='"+rollno+"' AND ENTRY.OUTTIME is NULL");
ps.setTimestamp(4,sqlTime);
ps.executeUpdate();

#1