1

Is it possible to insert values to one table and update another table with a single mysql call? If yes, is this faster or is it faster to use two separate calls?

For Example:

table1 - cars

id - color - brand

1 - red - audi

2 - blue - pontiac

table2 - people

id - name - last

1 - dave - ann

2 - beth - elane

tables do not relate

and lets say i would like to add another row to people while same time updating table1 cars's color

is that possible ?

Joel
  • 4,732
  • 9
  • 39
  • 54
Neta Meta
  • 4,001
  • 9
  • 42
  • 67
  • 4
    You can't combine them into one **statement** but you could do one **call** using [`mysqli_multi_query()`](http://php.net/manual/en/mysqli.multi-query.php). I have no idea how much faster it would be. – Travesty3 Dec 31 '12 at 19:26
  • I see. is there an equivalent of that in PDO ? – Neta Meta Dec 31 '12 at 19:28
  • Not familiar with it, but a quick search provides an example using PDO_MYSQLND: http://stackoverflow.com/a/6461110/259457 – Travesty3 Dec 31 '12 at 19:30
  • What do you mean at the same time, do you mean transaction, you'll get a lot more help if you give us some clue as to why you are asking this question. I for one am struggling to come up with a problem where this would be a solution... – Tony Hopkinson Dec 31 '12 at 19:32
  • well there isn't a problem i thought i'll check if its possible to run those 2 queries same statement to save time. – Neta Meta Dec 31 '12 at 19:32
  • Hmm Same time becomes save time. Putting them together means one round trip, instead of two. That's all you are are going to save. Only way to find out if that's significant is to benchmark, and if is I'd spend some time fixing the underlying reason as that will improve performance across the board. – Tony Hopkinson Dec 31 '12 at 19:55
  • Oh and round trip, is Apache to Mysql, this is all server side. – Tony Hopkinson Dec 31 '12 at 19:58

2 Answers2

0

Use a stored procedure... Something like...

DROP PROCEDURE IF EXISTS insert_update $$
CREATE PROCEDURE insert_update
(
  IN id INT,
  IN color VARCHAR(10),
  IN name VARCHAR(20)
)
BEGIN
    -- do insert
THEN
    -- do update
END IF;

Note: I haven't written your queries for you because you didn't supply any cogent information about your tables, per se.

Ian Atkin
  • 6,302
  • 2
  • 17
  • 24
0

In terms of semantics, I think you want perform a transaction not a single query.

If all your data is MyISAM, STOP RIGHT HERE. It is no possible.

If all you data is InnoDB, there are two paradigms to could set up:

PARADIGM #1

START TRANSACTION;
INSERT INTO people ...
INSERT INTO cars ...
COMMIT;

PARADIGM #2

Do the following within the DB Session:

SET autocommit = 0;

then do all the INSERTs you want then run

COMMIT;

If you close the DB Connection, everything will rollback. So, try to commit often.

RolandoMySQLDBA
  • 43,883
  • 16
  • 91
  • 132