Link to home
Create AccountLog in
Avatar of kpisor
kpisorFlag for Japan

asked on

Loop for processing MySQL row inserts

I'm looking for some help in writing a PROCEDURE in MySQL that will step through rows from a temporary table 'temp_table':

temp_table.t_firstname_en,
temp_table.t_lastname_en,
temp_table.t_email

... and INSERT the rows that do not have a matching 't_email' value. So in other words, no duplicates would be created.

FOR ($i = 0, $i < count(temp_table), $i++) {
IF (temp_table.t_email[$i] != contact.t_email[$i]) {

INSERT INTO contact.t_firstname_en[$i] VALUE temp_table.t_firstname_en[$i],
INSERT INTO contact.t_lastname_en[$i] VALUE temp_table.t_lastname_en[$i],
...

}

That sort of thing. I know my syntax is wrong, I need some help from someone who already knows how to do this.

This needs to be a PROCEDURE so that I can run it a couple of times/year.

Thanks!

karl
Avatar of tipsybroom
tipsybroom
Flag of Germany image

Avatar of Sara bhai
For this type generally used cursor. But did not o for that till processing is so complex.
You can do it using following
 
INSERT INTO contact( t_firstname_en,t_lastname_en) VALUES
Select t_firstname_en , t_lastname_en from temp_table
Where  temp_table.t_email != contact.t_email
Avatar of kpisor

ASKER

Hi all,

I was hoping for something a bit more concrete. Could anyone *write* sample MySQL cursor code that does what I'm trying to do? tipsybroom seems to be on the right track.

I actually need to clarify my request. I need to check my entire temp_table against my target table for matching emails. So for *each* row in temp_table, I need to look at the *entire* target table. If there is a matching email, then do not make a new INSERT. If there is no matching email, we want a new INSERT.

Please help! 10 lines of sample code to get me started, and I give you 500 points. Shouldn't be too hard for you gurus.

Karl
Hi check out my article below, it actually tells about the care to be taken while using a cursor in Mysql but it might help you in writing a basic cursor and understand how it works.

http://www.experts-exchange.com/Database/MySQL/A_11314-Care-while-using-cursor-in-mysql.html
ASKER CERTIFIED SOLUTION
Avatar of Aaron Tomosky
Aaron Tomosky
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Rather than looping, just put a unique index on the target table and do a bulk insert from all the rows of the temp table.
Avatar of kpisor

ASKER

OK, this is what I've come up with. I have a temp table with the records I want to insert.

However, when I do the query, I am getting
Affected rows : 0, Time: 0.08sec

Please help!

INSERT INTO contact (
  contact.fk_prefecture,
  contact.t_lastname_jp,
  contact.t_firstname_jp,
  contact.t_firstname_en,
  contact.t_lastname_en,
  contact.t_email,
  contact.t_phone,
  contact.t_phone_emergency,
  contact.t_postalcode,
  contact.t_city,
  contact.t_address1,
  contact.t_address2
)
 SELECT
contact_temp.fk_prefecture,
  contact_temp.t_lastname_jp,
  contact_temp.t_firstname_jp,
  contact_temp.t_firstname_en,
  contact_temp.t_lastname_en,
  contact_temp.t_email,
  contact_temp.t_phone,
  contact_temp.t_phone_emergency,
  contact_temp.t_postalcode,
  contact_temp.t_city,
  contact_temp.t_address1,
  contact_temp.t_address2
 FROM `contact_temp`
 WHERE contact_temp.t_email NOT IN (select contact.t_email from contact)