CODEWITHSUNDEEP
sql
user543666
user543666

Reputation: 21

How can I insert into one table and update another table with the same sql statement?

The requirement to do it in one statement is because of how the program handles sql statements. The sql statement is stored in a column of another table, and executed through an open on a recordset. The open responds with an error of invalid character if a semi-colon is in the statement.

The scenario: Under certain conditions, I want to update a particular field in one record in database A, and record the fact of that change in a log table by an insert.

Here's an example using two statements:

update data_table a set field1='new value' where identifier=10;

insert into log_table (action_taken) 
values('record ' || a.identifier || ' had field1 changed to ' || a.field1);

Is there any way to do this?

Upvotes: 2

Views: 3730

Answers (4)

Dan
Dan

Reputation: 11069

If your database happens to be Oracle you may be able to use an anonymous PL/SQL block if for some reason you can't/don't want to create a stored procedure:

BEGIN
 update data_table a set field1='new value' where identifier=10;

 insert into log_table (action_taken) 
  values('record ' || a.identifier || ' had field1 changed to ' || a.field1);
END;

Upvotes: 0

Max Kielland
Max Kielland

Reputation: 5841

I think you are looking for TRIGGERS. Without knowing what database you are using I can only guess.

Here is information about triggers for MySQL.

A trigger is tied to a table to start on a specific event, such as INSERT, UPDATE or DELETE. The trigger can then run one or more SQL statements.

And here is how you create a trigger

Upvotes: 2

DVD
DVD

Reputation: 1784

Make a batch call to the SGDB you can do what you have done, if you are using java or .net both of the their SQL API support sql batch commands.

update data_table a set field1='new value' where identifier=10;
GO
insert into log_table (action_taken) 
values('record ' || a.identifier || ' had field1 changed to ' || a.field1);
GO

But if you are going to do this two operations at the same time all the time you should accept krefftc answer because it's the best way

Upvotes: 0

capdragon
capdragon

Reputation: 14899

Put them both in a stored procedure and execute call the stored procedure.

Upvotes: 3

Related Questions