Problem with SELECT INTO using local variables

Question

I need to create an SQL query to insert some data into a table based on the results of a SELECT query into a local variable. My DB platform is MySQL 5.1, but I don't think that should make a difference here. Basically, what I want to do is:

SELECT id INTO var_name FROM table1 WHERE some_column='something' LIMIT 1;
INSERT INTO table2 (`number`) VALUES (@var_name);

I can't get past the first statement, though, as I get the error "Undeclared variable var_name". I've also tried putting the statement in a BEGIN/END block, and a stored procedure, but then I encounter other errors which state only that there is an error on the SELECT line. What am I doing wrong?

Andrew Hare · Accepted Answer · 2009-01-23 13:58:26Z

3

You need to declare @var_name and then select into @var_name. I don't know MySQL too well but try something like this:

declare @var_name varchar(100);
SELECT id INTO @var_name FROM table1 WHERE some_column='something' LIMIT 1;
INSERT INTO table2 (`number`) VALUES (@var_name);

Edit: Perhaps a better way to do it:

insert into table2 ('number')
select id 
from table1 
where some_column = 'something' LIMIT 1;

This negates the need for a variable and will be a lot faster and easier to understand down the road.

edited Jan 23, 2009 at 13:58

answered Jan 23, 2009 at 13:51

Andrew Hare

353k75 gold badges649 silver badges642 bronze badges

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

7 Comments

Tomalak Over a year ago

Why should he even use a variable at all?

Nik Reiman Over a year ago

When I tried this, I get an error saying "Undeclared variable: id". Also when I try surrounding it with backticks id. Why is this?

Andrew Hare Over a year ago

Good point - I don't think he does for this example but he did ask how to do it.

Nik Reiman Over a year ago

Yeah, actually, I didn't need a variable, it turns out. Sorry; I marked the other answer as correct, as it's the better solution here. :)

Tomalak Over a year ago

I think you misunderstood the SELECT INTO syntax a bit. It is for storing the results in a new table.

|

Tomalak · Accepted Answer · 2009-01-23 13:53:55Z

2

Try

INSERT INTO table2 (`number`)
SELECT id FROM table1 WHERE some_column='something' LIMIT 1

answered Jan 23, 2009 at 13:53

Tomalak

339k68 gold badges547 silver badges635 bronze badges

1 Comment

Andrew Hare Over a year ago

Ah - beat me to it - very nice!

Fabiano Shark · Accepted Answer · 2012-12-10 18:36:28Z

-2

CREATE TABLE table_name
AS  
SELECT ...(your select)

answered Dec 10, 2012 at 18:36

Fabiano Shark

3842 silver badges6 bronze badges

1 Comment

saluce Over a year ago

This assumes the table hasn't been created yet, which the OP didn't mention.

Collectives™ on Stack Overflow

Problem with SELECT INTO using local variables

3 Answers 3

Edit: Perhaps a better way to do it:

7 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Edit: Perhaps a better way to do it:

7 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related