Batch Update Scenario Issues

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 批量更新场景问题

| username: clearcodecn

【TiDB Usage Environment】Production Environment
【TiDB Version】6.1
【Reproduction Path】
【Encountered Problem: Problem Phenomenon and Impact】
There is a batch update scenario in the business, and the TiDB community provides batch update statements Batch Update. This scenario updates columns that meet multiple conditions to the same value. The problem I am encountering now is whether it supports updating columns with multiple conditions to corresponding values:

create table example (id int primary key , val varchar(255) not null default '');

insert into example(id,val) values (1,'1'), (2,'2'), (3,'3');
select * from example

-- This is the currently supported syntax
update example set val = 'updated' where (id,val) in (1,'1'), (2,'2'), (3,'3')

-- Expected update method,
update example set ( val = 'update1' ), (val = 'update2') , (val = 'update3') where (id,val) in (1,'1'), (2,'2'), (3,'3')

I wonder if TiDB supports such multi-column updates with different values,
otherwise, the business can only perform updates in a loop.

2
| username: xfworld | Original post link

Supporting multi-column updates, for TiDB, multiple columns are just a single row of data…
This syntax is consistent with MySQL.

image
image932×1000 43.2 KB

However, you need to control the size of the updated data rows, as this is related to transactions.
Overly large transactions may fail…

3
| username: hey-hoho | Original post link

I feel that this way of writing is not intuitive. The possibility of errors increases when there are more conditions after “in”. It would be more practical to handle it line by line.

4
| username: buddyyuan | Original post link

This method works

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | aa1  |
|    2 | bb1  |
|    3 | cc1  |
+------+------+
3 rows in set (0.00 sec)

mysql> UPDATE t1
    -> SET name = (CASE name WHEN 'aa1' THEN 'aa2' WHEN 'bb1' THEN 'bb2'WHEN 'cc1' THEN 'cc2' END)
    -> WHERE id IN(1, 2 ,3);
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | aa2  |
|    2 | bb2  |
|    3 | cc2  |
+------+------+
3 rows in set (0.00 sec)
5
| username: clearcodecn | Original post link

Thank you.

6
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.