Update and delete together on PostgreSQL

user_11379189 0
Viewed 2041

I have a problem on my postgreSQL query. I must do a query with an UPDATE before and a DELETE after. The return value is the number of UPDATEs but I want the count of DELETEs. How can I do? Thank you very much. I use myBatis.

<update id="updateSpotMetaDataTagIdsToDelete">
    <bind name="simulatedById" value="_parameter.spotUpdate.userOwner.getSimulatedById()" />

    <foreach item="id" collection="spotUpdate.spotMetaDataTagIdsToDelete" separator=";" >
        UPDATE spotsmetadatatags
        SET updatedate = now(),
        simulatedBy = #{simulatedById},
        fkuserowner = #{spotUpdate.userOwner.id},
        sessionId = #{spotUpdate.userOwner.sessionId}
        WHERE
        id = #{id}
        AND fkIdSpot = #{spotUpdate.fkIdSpot}
        AND (SELECT COUNT(*)
        FROM spots as s
        WHERE s.id = #{spotUpdate.fkIdSpot}
        AND s.fkIdOrganization = #{spotUpdate.userOwner.organization.id}) = 1
    </foreach>;

    DELETE FROM spotsmetadatatags
    WHERE fkIdSpot = #{spotUpdate.fkIdSpot}
    AND id IN (
    <foreach item="id" collection="spotUpdate.spotMetaDataTagIdsToDelete" separator="," >
        #{id}
    </foreach>
    )
    AND (SELECT COUNT(*)
    FROM spots as s
    WHERE s.id = #{spotUpdate.fkIdSpot}
    AND s.fkIdOrganization = #{spotUpdate.userOwner.organization.id}) = 1;
</update>
mybatis postgresql java
2 Answers

In this particular case you can do what you want. From the queries above I can see that:

  1. records are updated first one by one with the same values
  2. the same records are deleted

This has some inefficiencies:

  1. updating each record using a separate query cause a number of network calls
  2. the predicate in UPDATE and DELETE is the same which makes it harder to maintain

This can be addressed like this:

<delete id="updateSpotMetaDataTagIdsToDelete">
    <bind name="simulatedById" value="_parameter.spotUpdate.userOwner.getSimulatedById()" />

    with updated as (
        UPDATE spotsmetadatatags
        SET updatedate = now(),
        simulatedBy = #{simulatedById},
        fkuserowner = #{spotUpdate.userOwner.id},
        sessionId = #{spotUpdate.userOwner.sessionId}
        WHERE
        id IN (
          <foreach item="id" collection="spotUpdate.spotMetaDataTagIdsToDelete" separator="," >
           #{id}
          </foreach>
        )
        AND fkIdSpot = #{spotUpdate.fkIdSpot}
        AND (SELECT COUNT(*)
        FROM spots as s
        WHERE s.id = #{spotUpdate.fkIdSpot}
        AND s.fkIdOrganization = #{spotUpdate.userOwner.organization.id}) = 1
        RETURNING id
    )   
    DELETE FROM spotsmetadatatags AS s
    USING updated AS u
    WHERE u.id = s.id
</delete>

This way you remove duplication, only one network call instead of N is executed and the query will return the number of delete records.

user_22319720

You can execute multiple SQL statements in single go but It should belong to same operation like Multiple Update statements or multiple Delete statements. You can not mixed update and delete in same mapper.

Example If you want to delete with multiple sql statements.

<delete id="delete" parameterType="map">
    DELETE FROM table_a where X=${frommap};
    DELETE FROM table_b where X=${frommap};
    DELETE FROM table_c where X=${frommap}
</delete>
user_114146190
Related