mysql - "where in" in trigger after insert -

i must insert new row in table, text column contains reference table's ids. eg: insert table1 (reference, date) values('23,24,25','2016-09-22');

my trigger should update table2.status table2.id 1 of table1.reference values. it's this:

delimiter ;; create trigger `rim_ai` after insert on `table1` each row update table2 set status = 11 id in (new.reference);; delimiter ; 

but... found status value changed on first row (eg. 1 id 23). if broken in trigger should update nothing @ all! should convert reference field else 'text'? in advance.

it's not trigger broken, table design. should not store series of values within single field separated comma (or other separator). should store each individual reference - date pair in own record.

instead of

reference | date 23,24,25  |2016-09-22 

have

reference | date 23        |2016-09-22 24        |2016-09-22 25        |2016-09-22 

in instance trigger work expected. although, rewrite where clause slightly:

...where id = new.reference 

you technically make trigger work current data structure using multi-table update statement , find_in_set() function, not recommend that.

the cause behaviour experiencing id in (new.reference) expression not evaluated id in (23,24,25) (set of 3 values in in operator), id in ('23,24,25') (single string containing 3 numbers). mysql silently converts '23,24,25' string number, results in number 23 (comma after 23 cannot interpreted part of number, mysql stops conversion returns 23 number). hence record id=23 updated.