Writing a conditional insert statement

Question

update A set x = '0' where [condition]; 

if the where clause is not met, the update fails.

depending on this, I want to call an insert into another table, but only if the update was performed - that is, ROW_COUNT ()> 0.

How can I do this with a single request?

I tried this:

 update A set x = '0' where [condition]; if row_count() > 0 then insert into [...]; end if; 

this leads to an error.

PS:

The question is exclusively about the ability to perform the update and conditional insertion into one db request. SQL-Injection-safty is provided using prepared statements.

Answer 1

Create a stored procedure, for example:

 DELIMITER $$ CREATE PROCEDURE updateA (c1 varchar) BEGIN declare rows_affected integer; UPDATE a SET x = '0' WHERE col1 = c1; SELECT row_count() INTO rows_affected; IF rows_affected > 0 THEN BEGIN INSERT INTO ..... END; END IF; END $$ DELIMITER ; 

Or use the AFTER UPDATE trigger

 DELIMITER $$ CREATE TRIGGER au_a_each AFTER UPDATE ON a FOR EACH ROW BEGIN INSERT INTO b (x,a_id) VALUES (new.x, new.id); END $$ DELIMITER ; 

Answer 2

you can do this with two sql statements, if your condition is mutually exclusive, that is, if it "allows" the update, then by definition it excludes insertion and vice versa.

eg.

 UPDATE your_table set col1 = 'x' where condition = <some condition here> ; INSERT your_table (col1) select x from some_other_table where condition = <some condition here> ;