How to combine two unrelated SQL queries

Question

Let's say I have two SQL queries:

INSERT INTO tableA VALUES ('', 'mike', '21')

DELETE FROM tableB WHERE name = 'john'

And I want to execute them at the same time. How do I do that? I know that UNION only works for SELECT statements, so it's useless in this case.

"And I want to execute them at the same time" --- any **real** reason to want that? Or is it just a caprice — zerkms, Oct 07 '11 at 05:03
I'm making a scheduled event in MySQL that executes every 24 hours and has to do a lot of stuff. I'll either have to make a big query or I'll have to make multiple events... — Bruno Sacks, Oct 07 '11 at 05:06

score 6 · Accepted Answer · answered Oct 07 '11 at 05:07

6

You create a transaction.

http://dev.mysql.com/doc/refman/5.5/en/commit.html

START TRANSACTION;
  INSERT INTO tableA VALUES ('', 'mike', '21');
  DELETE FROM tableB WHERE name = 'john';
COMMIT;

answered Oct 07 '11 at 05:07

tereško

58,060
25
98
150

Great, it was simpler than I thought. Thanks to everyone that answered! – Bruno Sacks Oct 07 '11 at 05:18

score 0 · Answer 2 · answered Oct 07 '11 at 05:07

0

Write a stored procedure that encapsulates these statements and call that from your application.

answered Oct 07 '11 at 05:07

Saket

45,521
12
59
79

score 0 · Answer 3 · answered Oct 07 '11 at 05:08

0

Put all statements in a stored procedure and call that procedure instead.

answered Oct 07 '11 at 05:08

Albin Sunnanbo

46,430
8
69
108

paxdiablo · Answer 4 · 2011-10-07T07:29:36.307

0

You need to look into using transactions. This is the A (atomicity) in the ACID properties that guarantee transactional integrity in databases, meaning that either all of a transaction will happen or none of it will.

Check out the latest docs for a description of how this works. Basically, it's:

start transaction
do your first operation
do your second operation
commit

And, with proper isolation, the changes won't be visible to the outside world until the commit is done.

edited Oct 07 '11 at 07:29

answered Oct 07 '11 at 05:08

paxdiablo

854,327
234
1,573
1,953

"the relational database ACID properties" is almost certainly a misstatement. Hints: 'database' = the collection of data, 'DBMS' = the software system managing the data, 'relational' = a word not found in the SQL Standard specs. Suggestion: replace "relational database" with "DBMS". – onedaywhen Oct 07 '11 at 07:25

score 0 · Answer 5 · edited May 23 '17 at 12:19

0

You can't execute them both at exactly the same time, but I'm guessing you're asking this because you only want to run the second query if the first one succeeds. This can be achieved by using transactions. There are some good examples is this question.

edited May 23 '17 at 12:19

Community

1
1

answered Oct 07 '11 at 05:08

Kaivosukeltaja

15,541
4
40
70

How to combine two unrelated SQL queries

5 Answers5