[FIXED] JDBC, spring: How to bulk update using JdbcTemplate

Issue

I'm trying to update one Column data with the value. CARD Table Example likes below.

| id | column_for_update |
| 1 | 10 |
| 2 | 11 |
| 3 | 12 |
| 4 | 13 |
| 5 | 14 |
...

Using jdbcTemplate(or other possible templates), I wanted to update column_for_update to 1000 * n(n = 1, 2, 3 ..), and result will be like ['10, 11, 12 ...'] -> ['1000, 2000, 3000'].

It is possible importing all objects into the application level and update them, but importing a large number of objects causes outOfMemory exceptions. How can I update this columns efficiently?

Solution

You can try changing your query to use a UPDATE/SELECT to multiply your id column by 1000:

UPDATE card a
INNER JOIN card b ON a.id = b.id
SET a.column_for_update = (b.id * 1000)

id	column for update
1	1000
2	2000
3	3000
4	4000
5	5000

See Fiddle.

Answered By - GRIV
Answer Checked By - Candace Johnson (JavaFixing Volunteer)