为什么我的SQL查询不起作用? [英] Why is my SQL Query not working?

问题描述

我正在尝试从通过主键链接的两个不同的表中删除.

我的表格如下..

表格名称:StudentCourse

PK:StudentID
学生姓名
StudentPassword
CourseCode


表名称:StudentModuleResult

PK:StudentID
ModuleCode
ResultCode


我具有以下功能:

I am trying to delete from two different tables which are linked by the primary key.

My tables are as follows..

Table Name: StudentCourse

PK: StudentID
StudentName
StudentPassword
CourseCode


Table Name: StudentModuleResult

PK: StudentID
ModuleCode
ResultCode


I have the following function:

public String deleteStudent(String studentid) {
        int noOfUpdates = 0;
        String message = "";
        try {
            String strQuery = "DELETE FROM StudentCourse WHERE StudentID = " + studentid
                            + " DELETE FROM StudentModuleResult WHERE StudentID = " + studentid;

            PreparedStatement stmt = conn.prepareStatement(strQuery);
            stmt.setString(1, studentid);
            noOfUpdates = stmt.executeUpdate();
        }
        catch (SQLException e) {
            message = "SQL Exception: " + e.getMessage();
        }
        if (message.length() ==0 && noOfUpdates == 0)
            message = "Record was not updated";

        return message;
    }

但是，我收到以下错误:

SQL异常:查询表达式"StudentID = 10198765 DELETE FROM StudentModuleResult WHERE StudentID = 10198765"中的[Microsoft] [ODBC Microsoft Access Driver]语法错误(缺少运算符).

However I am receiving the following error:

SQL Exception: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''StudentID = 10198765 DELETE FROM StudentModuleResult WHERE StudentID = 10198765''.

推荐答案

尽管您可以一次完成此操作，但我还是建议您:
-创建一个存储过程以进行删除并将密钥作为参数传递
-分别从客户端运行命令.

其他注意事项:
-在SQL语句中使用参数代替文字
-考虑是否真的需要2个具有相同主键(1:1关系)的表

While you can do this with a single I would suggest that you either:
- create a stored procedure to do the deletions and pass the key as parameter
- run the commands from client side separately.

Few other notes:
- use parameters instead of literals in the SQL statements
- Consider if you really need 2 tables with the same primary key (1:1 relation)

为什么要使用stmt.setString?您的SQL语句不包含参数，其次，您尝试执行两个SQL DELETE语句.如果语法允许的话
可能缺少分隔符.
尝试分别执行两个DELETE语句.您尚未指出外键的用法，但是如果存在外键，则必须先从具有外键约束的表中删除.

最好的问候，

Why are you using stmt.setString? Your SQL statement does not contain a parameter and secondly you are trying to execute two SQL DELETE statements. If the syntax allows for this there
is probably a separator missing.
Try executing the two DELETE statments separately. You have not indicated the usage of a foreign key, but if one exists you have to delete from the table that has the foreign key constraint first.

Best Regards,

http: //stackstackflow.com/questions/1509949/deleting-from-2-tables-at-the-same-time [级联删除

如果我误解了您的问题，请随时纠正我.

http://stackoverflow.com/questions/1509949/deleting-from-2-tables-at-the-same-time[^]

cascade delete

If i misunderstand your question, please feel free to correct me.

这篇关于为什么我的SQL查询不起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！