writing a conditional insert statement

Question

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.

no. the code is sufficient. my question is specifically about how to write a query with a conditional statement. — Raffael, Jun 05 '11 at 12:16
which version of mysql are you using. Also, what is the error? Do you get the same error if you run- select row_count()? — Jai, Jun 05 '11 at 12:20
select row_count() is fine. row_count is an inbuilt mysql-function BTW. it's a syntax error and I use the most recent version. — Raffael, Jun 05 '11 at 12:23
@Mat: best answer so far here, OP question is really not acceptable. — Micromega, Jun 05 '11 at 16:24

Johan · Accepted Answer · 2011-06-05T12:36:07.663

4

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 ;

edited Jun 05 '11 at 12:36

answered Jun 05 '11 at 12:30

Johan

74,508
24
191
319

(+1 of course). thanks for the SP but triggers won't do the trick for two reasons. a trigger is invoked if the update was succesful. though my update will alway be succesful as in not leading to an error. i need a different criteria for the insert. also i need to insert the users IP which is not accessible for the trigger. – Raffael Jun 06 '11 at 13:12
1

@Raffael, If the `where` clause is not true, the update trigger does not get called. – Johan Jun 06 '11 at 13:43
really ... oh that's interesting. I'll check that! – Raffael Jun 06 '11 at 14:23

score 0 · Answer 2 · edited Jun 05 '11 at 13:26

0

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

edited Jun 05 '11 at 13:26

Johan

74,508
24
191
319

answered Jun 05 '11 at 12:35

davek

22,499
9
75
95

writing a conditional insert statement

2 Answers2