CODEWITHSUNDEEP
sqlsql-updatesql-insert
NARU
NARU

Reputation: 2829

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 existing 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!

Upvotes: 1

Views: 3120

Answers (4)

9000
9000

Reputation: 40884

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).

Upvotes: 3

BCsongor
BCsongor

Reputation: 869

Yes, it's possible with stored procedures.

Watch this: Stored procedures

Upvotes: 1

Vaandu
Vaandu

Reputation: 4935

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

Upvotes: 1

Tudor Constantin
Tudor Constantin

Reputation: 26861

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.

Upvotes: 4

Related Questions