CODEWITHSUNDEEP
mysqlsqlstored-proceduresmysql-workbench
Suren Raju
Suren Raju

Reputation: 3060

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

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?

Upvotes: 0

Views: 2318

Answers (1)

Melon
Melon

Reputation: 883

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;

Upvotes: 1

Related Questions