0

I have a mysql table with fields of the form (id, contact_id, fruit, preference_no)

Id is auto-incrementing, contact_id, fruit and preference_no can take many values.

I want to insert many rows into this table. These rows all have fruit = 'apple' and preference_no = '7" but the value for contact_id is to to be drawn from a list of numbers ie contact_id in (523, 525, 526, 539,...)

So the first row to insert would be (523,'apple',7) and the second row would be (525,'apple', 7) etc

Is there a quick way to do this using MySQL?

Can I do something with parameters?

Eg Insert into table (contact_id, fruit, preference_no) select @param, 'apple', 7 where @param = (523,525,526,539,...)

JoAnne
  • 194
  • 9
  • From where are coming this data? from another table? from application? etc. ? – Oto Shavadze Mar 16 '17 at 12:37
  • It is just a list of values (523,526,528,531,...). so i want to insert (523, apple, 7) as one row and (526, apple, 7) as another row etc. – JoAnne Mar 16 '17 at 12:42
  • So, if value_2 and value_3 are same, you want just ignore and not insert? – Oto Shavadze Mar 16 '17 at 12:46
  • 1
    Use of a scripting language like PHP is the best option. – user3741598 Mar 16 '17 at 12:52
  • Question reworded to clarify the problem. – JoAnne Mar 16 '17 at 13:07
  • what about a stored `procedure similar` to http://stackoverflow.com/questions/186756/generating-a-range-of-numbers-in-mysql or http://stackoverflow.com/questions/2075887/auto-insert-values-to-table-from-range but in yours add apple and 7 to the insert part? – Dave Goten Mar 16 '17 at 13:29

2 Answers2

-1

Multiple inserts are done like this:

INSERT INTO 
  tablename  
  (contact_id, fruit, preference_no)
VALUES 
  (523, 'apple', 7), (526, 'apple', 7) ...
num8er
  • 18,604
  • 3
  • 43
  • 57
  • This doesn't seem like a quick way when there are over 300 rows to insert. – JoAnne Mar 16 '17 at 13:16
  • @JoAnne There's​ no quick way. Maybe You want to look at `LOAD DATA INFILE` ? In Enterprise grade apps such things done by queueing (rabbitmq, amqp, rsmq) – num8er Mar 16 '17 at 13:23
-1

Alter table to make 'Apple' and '7' as default values of fruit and preference_no then iterate into list to produce the following:

INSERT INTO table (contact_id) VALUES 
(523),(525),(526),(539)
tatskie
  • 405
  • 1
  • 7
  • 16