How do I UPDATE from a SELECT in JPA Custom Query

user_13152124 0
Viewed 103

I want to update work_withdraw table .My aim to perform update select function at a same time.I want to set my Deposit table pending column data in work_withdraw quantity column , when user call the update api

.enter image description here

enter image description here

enter image description here

I want to set pending data in work_withdraw table quantity column. Here is my query But I am not able to update work_withdraw table through using JPA query DepositRepository.kt

package com.nilmani.workload.repository

import com.nilmani.workload.entity.Deposit
import com.nilmani.workload.entity.WorkWithdraw
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.Modifying
import org.springframework.data.jpa.repository.Query
import org.springframework.transaction.annotation.Transactional
import org.springframework.web.bind.annotation.RequestParam

interface DepositRepository:JpaRepository<Deposit,Long> {

    @Modifying
    @Transactional
    @Query("update Ww SET Ww .quantity = d .pending FROM WorkWithdraw AS Ww INNER JOIN  Deposit AS d  ON Ww.id = d.id")
    fun getPendingStatus(@RequestParam("id")id: WorkWithdraw):Long

}

I want update the table worker_withdraw automatically when admin enter the worker_withdraw id But My query Not existing as what Type of Result I want

spring-data-jpa mysql
1 Answers

JPA doesn't support update join syntax the way that many SQL databases do. Instead, you may use a subquery:

update WorkWithdraw ww
set ww.quantity = (select d.pending from Deposit d where d.id = ww.id);
user_18632290
Related