Multipe Update statements - MariaDB

Question

I use dbeaver to connect to a Mariadb. When I try to run a multi-line update statements, e.g.

update x set warehouse_id='WH02' where soh_id='f0b4d220';
update x set warehouse_id='WHU1' where soh_id='17482705';

I get the error 'Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '...''. If I run the same queries against the database connection created in MySQL workbench, it runs without any problem.

I assume that there is an issue with the usage of ';' but I cannot find a way of running multiple update statements in Mariadb. What am I doing wrong?

try: update x set warehouse_id='WHU1' where soh_id LIKE '%17482705%'; — young_protokaa42, Jul 27 '22 at 09:51
no, like is to have wildcards. The queries are correct with where. The point probably is on dbeaver side — Lelio Faieta, Jul 27 '22 at 09:53
Same, 'you have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' — eponkratova, Jul 27 '22 at 09:53
Found the solution - https://dbeaver.io/forum/viewtopic.php?f=2&t=1650 i.e. Alt + X — eponkratova, Jul 27 '22 at 10:08
Some SQL clients automatically treat a sequence of multiple SQL statements, separated by semicolons, as a single object (as far as their Run commands are concerned). Others -- DBeaver included -- treat a sequence of statements as a script and require you to run a script rather than a statement. — O. Jones, Jul 27 '22 at 10:29

score 0 · Accepted Answer · answered Jul 27 '22 at 10:09

0

To do a batch update in dbeaver, one needs to run script by selecting the queries and running Alt+X.

answered Jul 27 '22 at 10:09

eponkratova

467
7
20

score 0 · Answer 2 · answered Jul 27 '22 at 10:13

0

You could try this:

update x
set warehouse_id = case soh_id
 when 'f0b4d220' then 'WH02'
when '17482705' then 'WHU1'
end
where soh_id in ('f0b4d220', '17482705')

answered Jul 27 '22 at 10:13

Artur May

30
5

score 0 · Answer 3 · answered Jul 27 '22 at 10:18

when you have a unique key on the field soh_id you can use the INSERT INTO .... ON DUPLICATE KEY statement like this.

INSERT INTO x (soh_id, warehouse_id)
VALUES
   ('f0b4d220','WH02')
  ,('17482705','WHU1')
  ON DUPLICATE KEY UPDATE warehouse_id = VALUES(warehouse_id;

Multipe Update statements - MariaDB

3 Answers3