Need a sql statement to do upate and insert at the same time

I need a sql statement, to insert a new row in one database table and update an exist开发者_如何学运维ing row in another database table based on some conditions.

Is there a way to do this? To insert a row in one table and update a row in another database table in one sql statement?

Thanks in advance!

---

Yes, they are called Transactions, and are implemented with START TRANSACTION and COMMIT/ROLLBACK with something like:

START TRANSACTION;
  INSERT INTO ...
  UPDATE table2 SET name='TOTO' WHERE type=1;
COMMIT;

EDIT

This is not in fact one SQL query, but the operation is done atomically - and I think that is what you need.

---

A single SQL statement allows you to update one table, not several; if that statement is a MERGE then you can specify insert/update/delete actions but still targeting just the same one target table.

If you just want consistency, use transactions; until a transaction is committed, changes within it are not visible to the outside world.

If you want that a single update (which you cannot control) resulted in a coordinated insert, use an on update trigger in the table being updated. The trigger would insert appropriate row(s) into other table(s).

---

You can use Trigger to update second table on insert of first table

---

Yes, it's possible with stored procedures.

Watch this: Stored procedures