mySQL - Insert into three tables

Question

I recently asked this question.

I have a relational database with three tables. The first containts id's that relate to the second. The second contains id's that relate to the third. The third contains the results I am after.

Is it possible with a single query to query an id in the first table which gives all results from the third table that relate to it?

My chosen solution was:

select * from table1 t1 join table2 t2 on t1.t2ref = t2.id join table3 t3 on t2.t3ref = t3.id

Add a where clause to search for certain rows in table1

where t1.field = 'value'

My new question is:

I have realised that I need to insert into the three tables too. What I am dealing with is a reservation system. Is it possible to write a query that inserts into three tables directly after it queries them (using joins?).

Also another consideration I have is should I use transactions to ensure that two queries are run at the same time... both find that the id's are 'unreserved' and then resulting in a double booking or is there a more simple way?

Answer 1

You can't insert into multiple tables with one query, You'll have to break it up to multiple queries.

Answer 2

You should definitely do the three inserts in a transaction. I would probably write a stored procedure to handle the inserts.

EDIT:

Here is an example of a stored procedure with a transaction. Note the use of LAST_INSERT_ID() to get the ID of the previously inserted record. This is only two tables, but you should be able to extend it to three tables.

DELIMITER //
CREATE PROCEDURE new_engineer_with_task(
  first CHAR(35), last CHAR(35), email CHAR(255), tool_id INT)
BEGIN
START TRANSACTION;
   INSERT INTO engineers (firstname, lastname, email) 
     VALUES(first, last, email);

   INSERT INTO tasks (engineer_id, tool_id) 
     VALUES(LAST_INSERT_ID(), tool_id);
COMMIT;
END//
DELIMITER ;

And you call it like so:

CALL new_engineer_with_task('Jerry', 'Fernholz', '[email protected]', 1);

Answer 3

You always use transactions when performing multiple updates. If one update fails you will want to roll back previous successful updates so you don't violate any unenforced constraints of the relational model.