CODEWITHSUNDEEP
mysql
Raffael
Raffael

Reputation: 20045

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.

Upvotes: 4

Views: 872

Answers (2)

davek
davek

Reputation: 22925

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

Upvotes: 0

Johan
Johan

Reputation: 76753

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 ;

Upvotes: 4

Related Questions