For Help: seems that multiple rows write-back id not effeted when use `ON DUPLICATE KEY UPDATE`

23 views
Skip to first unread message

World Hello

unread,
May 10, 2022, 9:22:15 AMMay 10
to mybatis-user

*mybatis-version: 3.5.5*

I try to batch insert records and write back id into object. But I found that not all id can be write back (In my test, I insert two records but only one id was written back).
Here is my sql:
```
<insert id="batchInsert" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO month (month, game_id)
        VALUES
        <foreach collection="list" item="f" separator=",">
            (#{f.month}, #{f.gameId})
        </foreach>
        ON DUPLICATE KEY UPDATE
        game_id = VALUES(game_id)
</insert>
```

Then when I delete the `ON DUPLICATE KEY UPDATE`, it was normal(all id can write back), new SQL:
```
<insert id="batchInsert" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO month (month, game_id)
        VALUES
        <foreach collection="list" item="f" separator=",">
            (#{f.month}, #{f.gameId})
        </foreach>
        ON DUPLICATE KEY UPDATE
        game_id = VALUES(game_id)
</insert>
```

Iwao AVE!

unread,
May 10, 2022, 12:13:33 PMMay 10
to mybatis-user

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/d8272cb8-6909-4d46-9506-42a6460e7a10n%40googlegroups.com.

Guy Rouillier

unread,
May 10, 2022, 6:41:44 PMMay 10
to mybati...@googlegroups.com
Your second example - "new SQL" - still has "ON DUPLICATE KEY UPDATE".  I see Iwao's response to your question.

Guy Rouillier
--

World Hello

unread,
May 10, 2022, 10:01:15 PMMay 10
to mybatis-user
Oh, I copy the wrong. In fact, I use no `on duplicate` like this:

```
<insert id="batchInsert" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO month (month, game_id)
        VALUES
        <foreach collection="list" item="f" separator=",">
            (#{f.month}, #{f.gameId})
        </foreach>
</insert>
```

World Hello

unread,
May 10, 2022, 11:41:57 PMMay 10
to mybatis-user
Thanks.As an alternative method, I will insertOrUpdate one by one, or combine `batchInsert` and `batchUpdate`  instead of `batchInsertOrUpdate`.
Reply all
Reply to author
Forward
0 new messages