JOOQ Update Table with condition of a joined Table

Question

I got two Tables like this: Enrollment and Student

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])
)

I now try to Update a bunch of Enrollments (the exam_passed column) with some lastnames, an Lecture_ID and a year. From this I got the idea to use the joined 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();       
    }

But I get an DataAccessException on the .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.

The Documentation did not help me since the condition relies on another table. Is there any hope to get this to work or do I need to get each Enrollment by it self and update it single handedly?

Answer 1

Using vendor specific syntax

The question you've linked is about MySQL. SQL Server does not support this kind of syntax, but you can achieve an equivalent semantics by using the UPDATE .. FROM clause. I.e. try this:

ctx.update(enrollment)        
    .set(enrollment.EXAM_PASSED, passed)
    .from(joined)
    .where(student.LASTNAME.in(studentName))
    .and(enrollment.YEAR.eq(year))
    .and(enrollment.LECTURE_ID.eq(BigInteger.valueOf(lidx)))
    .execute();

Notice that a LEFT JOIN does not make any sense here if you're turning it again into an INNER JOIN by having a predicate on the student table in your WHERE clause.

Using standard syntax

I personally prefer using standard SQL syntax whenever it is possible in these cases, as these UPDATE .. FROM or UPDATE .. JOIN statements can often be replaced by IN or EXISTS predicates. For example:

ctx.update(enrollment)        
    .set(enrollment.EXAM_PASSED, passed)
    .where(enrollment.STUDENT_ID.in(
        select(student.ID)
        .from(student)
        .and(student.LASTNAME.in(studentName))
    ))
    .and(enrollment.YEAR.eq(year))
    .and(enrollment.LECTURE_ID.eq(BigInteger.valueOf(lidx)))
    .execute();