开发者

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜