我试图用JPA查询中的Inner join更新一些记录，但是我们得到了一些异常

Question

这是MySQL查询：

UPDATE grademyadvisor.gma_error AS ge
        INNER JOIN
    grademyadvisor.yodlee_error_code AS yec ON yec.id = ge.yodlee_error_id 
SET 
    ge.is_resolved = 0
WHERE
    ge.client_id = 639
        AND yec.error_code = 402;

Spring数据JPA查询和获取异常。原因：java.lang.IllegalArgumentException：org.hibernate.hql.internal.ast.QuerySyntaxException：应为“SET”，但在第1行第39列附近发现“INNER”[UPDATE com.gma.domain.gmaerror AS ge INNER JOIN ge.yodleeErrorCode AS yec SET ge.isresolved=:isresolved WHERE ge.client.id=:clientId AND yec.errorCode=:errorCode]

@Query(" UPDATE GMAError AS ge "
        + "INNER JOIN ge.yodleeErrorCode AS yec "
        + "SET ge.isResolved=:isResolved "
        + "WHERE ge.client.id=:clientId "
        + "AND yec.errorCode=:errorCode ")

Answer 1

在WHERE子句中使用子查询来删除内部JOIN语句：

UPDATE grademyadvisor.gma_error
SET 
    is_resolved = 0
WHERE
    client_id = 639
AND yodlee_error_id = (SELECT id FROM grademyadvisor.yodlee_error_code WHERE error_code = 402);