mySQL multiple queries

[Stuart]

Hi,
is there any way to send multiple queries at once, like the way you can do with php or in the 'console' by separating them with a semicolon?
There are times that I want to be sure my queries are processed together etc.
Thank you!

 

[ArshilAhmad]

Hi @Stuart,

Could you please create a brief screen recording and provide further elaboration on this specific concern? This will help us gain a better understanding and assess the feasibility of your use case.

 

[Stuart]

a few examples off the bat:
1) where I want either both transactions to succeed or both fail:

START TRANSACTION;
INSERT INTO table1 (column1) VALUES ('value1');
INSERT INTO table2 (column2) VALUES ('value2');
COMMIT;

2) or in a case where I want to do multiple things with the same data:

INSERT INTO table1 (column1, column2) VALUES (value1, value2);
UPDATE table2 SET column1 = value1 WHERE condition;
DELETE FROM table3 WHERE condition;

3) multiple selects where ARRAYAGG or UNION won't work ( for example, because there are different number of columns in each table)

SELECT * FROM clients WHERE client_id = '1';
SELECT * FROM cases WHERE case_client_id ='1';

4) procedures

DELIMITER //
CREATE PROCEDURE procedure_example()
BEGIN
    SELECT * FROM table1 WHERE condition1;
    UPDATE table2 SET column1 = value1 WHERE condition2;
END //
DELIMITER ;

I am sure there are many more (security, log or audit layers)

 

[Pabblymember11]

In a standard SQL execution environment, each statement—be it INSERT, UPDATE, or DELETE—executes sequentially when submitted together as part of a single batch or script. Please give it a try at your end and let us know.

 

[Stuart]

I have tried it and get an error like this, even though each statement by itself works

 

[Stuart]

something else I want to do via mySQL is checklists, that would return back if the list was complete when a check item is marked.

 

[ArshilAhmad]

Please allow me some time to discuss the possibility of this use case with my team. We will get back to you with and update soon.

 

[ArshilAhmad]

Our team suggests that you follow these steps to insert data in bulk.

https://forum.pabbly.com/threads/mysql-having-a-problem-with-insert-into.20648/#post-99012

 

[Stuart]

Thank you, however that is a single query inserting multiple rows of data, whereas the ones here are multiple related queries chained together as mySQL allows with a semicolon.
where for example I want to insert a new client and case at the same time, but if one should fail the other one will also so as not to cause a cascade of failures when other automations come into play. or if I want to reference the previous query like

SELECT LAST_INSERT_ID();

where I would want to be sure that no one else accessed the database between the queries.

 

[ArshilAhmad]

Please allow us some time to look into this concern. We will get back to you with an update soon.

 

[ArshilAhmad]

I had a discussion with the technical team, and they informed me that it’s not possible to run multiple related queries in the same action step. They can only run in separate action steps.

 

[Stuart]

That is unfortunate, but thank you anyways.