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
精彩评论