mysql - How to insert multiple records with some conditions -

i have 2 tables. 1 table contains words. other table contains points.

table words:  id word   table points:  id wordid x y  

word column unique.

i want write stored procedure takes on input current x value , list of word|y values.

for example:

it's initial words table rows:

id word 1  carrot 2  apple 3  potato 

we call procedure storedata(x = 5, words = { carrot:123, onion:321 }).

as result have:

words table:    id word   1  carrot   2  apple   3  potato   4  onion    points table:     id wordid x  y  1    1    5 123  2    4    5 321 

how it?

you can't pass structured data parameter stored procedure: have first insert (temporary) table somewhere, read contents of table within procedure. such, may insert directly destination tables:

insert ignore words (word) values ('carrot'), ('onion');  insert points (wordid, x, y)   select words.id, 5, y     words natural join (       select 'carrot' word, 123 y     union       select 'onion'  word, 321 y   ) t; 

see on sqlfiddle.

(incidentally, materialised table t table you'd have create , populate before calling stored procedure):

delimiter ;; create procedure storedata(in x int) begin   insert ignore words select word args;   insert ignore points (wordid, x, y)     select words.id, 5, y words natural join args; end;; delimiter ;  drop   temporary table if exists args; create temporary table args (word varchar(20), y int); insert args values ('carrot', 123), ('onion', 321); call storedata(5);