How to use procedure variable in select and insert query in MySQL?

Question

I have two variables 1. inserted_user_id, 2. inserted_address_id

in my MySQL procedure.

I need to use them in insert queries and select queries. Im trying something like

insert into user_new(name, company, email, customer_id) select name, company, email, inserted_user_id from user_old;

insert into user_address_map(address_id, user_id) select inserted_user_id,inserted_address_id ;

There two statements are not working. How to use procedure variable's value in the above sql statements?

Hawk · Accepted Answer · 2014-01-07 11:11:31Z

1

First make a select to get all the info: (you need to declare these variables first)

SELECT name, 
       company, 
       email, 
INTO   varname, varcompany, varemail 
FROM   user_old

then use it into insert

INSERT INTO user_new 
            (name, 
             company, 
             email, 
             customer_id) 
VALUES      (varname, 
             varcompany, 
             varemail, 
             inserted_user_id);

edit: First insert the Selected values and get the id of the inserted row

INSERT INTO user_new 
            (name, 
             company, 
             email) 
SELECT name, 
       company, 
       email 
FROM   user_old 

SET out_param = last_insert_id();

Then update this row with your param

UPDATE user_new 
SET    customer_id = inserted_user_id 
WHERE  id = out_param;

edited Jan 7, 2014 at 11:11

Hawk

5,18014 gold badges55 silver badges78 bronze badges

answered Jan 7, 2014 at 9:35

Melon

8838 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Suren Raju Over a year ago

Actually i have huge column list in the select statement. And the stored procedure includes many such tables. If i create variables for each column it will become more than 1000 variables. It will be tough to handle. Is there any alternative approach for this?

Melon Over a year ago

that case try to merge it all:

insert into user_new select name as name, company as company, email as email, inserted_user_id as customer_id from user_old);

or something like, found in here link

Suren Raju Over a year ago

If i use inserted_user_id in a select statement it compiles but fails in run time.

Melon Over a year ago

what error give you ? are you shure its hav the value u whant?

Melon Over a year ago

another way its to insert the values from select first, and after that update de last inserted row with inserted_user_id. I change the answer to show it

|

Collectives™ on Stack Overflow

How to use procedure variable in select and insert query in MySQL?

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related