CODEWITHSUNDEEP
mysqlinsertprocedure
MarkO
MarkO

Reputation: 199

MySQl Procedure INSERT INTO from table and variable

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

Upvotes: 1

Views: 3157

Answers (1)

Ed Gibbs
Ed Gibbs

Reputation: 26343

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.

Upvotes: 1

Related Questions