writing a conditional insert statement

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

if the where-condition isn't fulfilled no update takes place.

depending on this I 开发者_如何学运维want to trigger an insert in another table, but only if an update was done - i.e. ROW_COUNT() > 0.

how may I do that in one 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 purely aming at a possibility to to execute an update and a conditional insert in one db-request. SQL-Injection-safty is assured by using prepared statements.

Create a stored procedure like:

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 an 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 ;

you can do it two sql statements, as long as your condition is mutually exclusive, i.e. if it "allows" an update, then it will by definition exclude an insert, and vice-versa.

e.g.

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>
;