我有两个这样的表格:入学人数和学生人数
create table [LECTURE_DB].[dbo].[ENROLLMENT](
[EXAM_PASSED] bit null default ((0)),[EXAM_TAKEN] bit null default ((0)),[YEAR] int not null,[LECTURE_ID] numeric(19) not null,[STUDENT_ID] numeric(19) not null,constraint [PK__ENROLLME__FE468F5B2739D489]
primary key (
[YEAR],[LECTURE_ID],[STUDENT_ID]
)
)
create table [LECTURE_DB].[dbo].[STUDENT](
[ID] numeric(19) identity(1,1) not null,[FIRSTNAME] varchar(255) null,[GENDER] varchar(255) null,[LASTNAME] varchar(255) null,[YEAR_OF_BIRTH] int null,constraint [PK__STUDENT__3214EC273493CFA7]
primary key ([ID])
)
我现在尝试用一些exam_passed
,一个lastname
和一个Lecture_ID
来更新一堆注册(year
列)。来自this的想法是使用联接的Table。
private void updateStudentExamPassed(boolean passed,int lidx,int year,String... studentName) {
Enrollment enrollment = Enrollment.ENROLLMENT;
Student student = Student.STUDENT;
Table<?> joined = enrollment.leftJoin(student).on(enrollment.STUDENT_ID.eq(student.ID));
ctx.update(joined)
.set(enrollment.EXAM_PASSED,passed)
.where(student.LASTNAME.in(studentName))
.and(enrollment.YEAR.eq(year))
.and(enrollment.LECTURE_ID.eq(BigInteger.valueOf(lidx)))
.execute();
}
但是我在DataaccessException
上得到了.execute()
Exception in thread "main" org.jooq.exception.DataaccessException: SQL [update [LECTURE_DB].[dbo].[ENROLLMENT] left outer join [LECTURE_DB].[dbo].[STUDENT] on [LECTURE_DB].[dbo].[ENROLLMENT].[STUDENT_ID] = [LECTURE_DB].[dbo].[STUDENT].[ID] set [LECTURE_DB].[dbo].[ENROLLMENT].[EXAM_PASSED] = ? where ([LECTURE_DB].[dbo].[STUDENT].[LASTNAME] in (?,?) and [LECTURE_DB].[dbo].[ENROLLMENT].[YEAR] = ? and [LECTURE_DB].[dbo].[ENROLLMENT].[LECTURE_ID] = ?)]; Falsche Syntax in der Nähe des left-Schlüsselworts.
at org.jooq_3.12.1.SQLSERVER2014.debug(Unknown Source)
at org.jooq.impl.Tools.translate(Tools.java:2717)
at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:755)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:383)
at org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:119)
at de.esteam.lecturedb.jooq.tasks.RecordFeaturesTask.updateStudentExamPassed(RecordFeaturesTask.java:42)
at de.esteam.lecturedb.jooq.tasks.RecordFeaturesTask.run(RecordFeaturesTask.java:28)
at de.esteam.lecturedb.jooq.common.LectureDBAnalysis.run(LectureDBAnalysis.java:100)
at de.esteam.lecturedb.jooq.common.LectureDBAnalysis.main(LectureDBAnalysis.java:56)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Falsche Syntax in der Nähe des left-Schlüsselworts.
文档没有帮助我,因为条件依赖于另一个表。是否有希望使这项工作正常进行,还是我需要自行获取每个注册并单手更新?