MySQl Procedure INSERT INTO from table and variable

Question

I have a MySQL Procedure that does an insert into a table using a select from another table. Thus

INSERT INTO my_tbl (fld1, fld2, fld3) SELECT fld1, fld2, fld3 FROM tbl_2 where tbl_2.id=33;

That works fine

However, I now need to insert another field into my_tbl from a value I have in a variable (set from a passed in parameter)

Can someone explain the syntax to mix the insert with fields from the select (which I have already) with the additional field that is a variable in the procedure.

Many thanks

Mark

Ed Gibbs · Accepted Answer · 2013-06-26 21:21:42Z

1

Just use the variable like you'd use a column or column expression, except if it's a string don't surround it with quotes. If you have, for example, the variable @idValue:

INSERT INTO my_tbl (fld1, fld2, fld3)
SELECT fld1, fld2, fld3 FROM tbl_2
where tbl_2.id = @idValue;

Or if you're updating, say for example you have a variable @fld2Value:

INSERT INTO my_tbl (fld1, fld2, fld3)
SELECT fld1, @fld2Value, fld3 FROM tbl_2
where tbl_2.id = @idValue;

This will insert tbl2.fld1, the value of variable @fld2Value, and tbl2.fld3 into my_tbl. The number of rows inserted will depend on the number of rows returned by the SELECT, and the @fld2Value will be the same for each inserted row.

edited Jun 26, 2013 at 21:21

answered Jun 26, 2013 at 20:29

Ed Gibbs

26.4k4 gold badges50 silver badges75 bronze badges

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

Collectives™ on Stack Overflow

MySQl Procedure INSERT INTO from table and variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related