update query with optional parameres

user_8745586 0
Viewed 639

i am trying to update rows with optional parameters, but i am getting error with below query.

@Modifying
@Transactional
@Query("UPDATE Visit visits SET "
        + " visits.approvalStatus is null OR visits.approvalStatus = :approvalStatus "
        + " , visits.declineReason is null OR visits.declineReason =:declineReason "
        + " WHERE visits.visitId in :visitIds ")
public int patchUpdate(@Param("approvalStatus") String approvalStatus,
        @Param("declineReason") String declineReason, @Param("visitIds") List<Integer> visitIds);

error logs :

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: expecting EQ, found 'is' near line 1, column 69 [UPDATE com.quest.vms.entity.Visit visits SET  visits.approvalStatus is null OR visits.approvalStatus = :approvalStatus  WHERE visits.visitId in :visitIds ]

Edited to add info : here i want to update a particular field when new data is not null, otherwise that parameter should be ignored.

Thanks for your help

spring-data-jpa spring-boot jpa java
3 Answers

is null is used for checking the field. For setting column on the basis of condition you can use:

UPDATE Visit visits SET "
        + "  visits.approvalStatus = CASE "
        + " WHEN visits.visitId in :visitIds THEN :approvalStatus "
        + " ELSE NULL "
        + " END"
user_145223910

There is a syntax error in the UPDATE query, the part to set a field value is missing.

Possibly a query using COALESCE function (or a native query using functions such as IsNull, NullIf, NVL) could be helpful:

@Query("UPDATE Visit visits SET "
     + "visits.approvalStatus = COALESCE(:approvalStatus, approvalStatus) "
     + "visits.declineReason = COALESCE(:declineReason, declineReason) "
     + " WHERE visits.visitId in :visitIds")
public int patchUpdate(
    @Param("approvalStatus") String approvalStatus,
    @Param("declineReason") String declineReason, 
    @Param("visitIds") List<Integer> visitIds
);

Or using similar CASE statement

@Query("UPDATE Visit visits SET "
     + "visits.approvalStatus = (CASE :approvalStatus WHEN NULL THEN approvalStatus ELSE :approvalStatus END) "
     + "visits.declineReason = (CASE :declineReason WHEN NULL THEN declineReason ELSE :declineReason END) "
     + " WHERE visits.visitId in :visitIds")
public int patchUpdate(
    @Param("approvalStatus") String approvalStatus,
    @Param("declineReason") String declineReason, 
    @Param("visitIds") List<Integer> visitIds
);
user_132798310

You can add a case condition while setting data like

UPDATE 
    Visit visits 
SET 
    visits.approvalStatus = (
        case 
            when :approvalStatus is null 
            then visits.approvalStatus 
            else :approvalStatus 
        end
    ),
    visits.declineReason = (
        case 
            when :declineReason is null 
            then visits.declineReason 
            else :declineReason 
        end
    ),
 WHERE visits.visitId in :visitIds

If passed value is null, you set the value you have in db, otherwhile set the passed value.

user_137558570
Related