CODEWITHSUNDEEP
sqlmysqlmysql5
Nik Reiman
Nik Reiman

Reputation: 40430

Problem with SELECT INTO using local variables

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?

Upvotes: 1

Views: 5598

Answers (3)

Fabiano Shark
Fabiano Shark

Reputation: 384

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

Upvotes: -2

Andrew Hare
Andrew Hare

Reputation: 351748

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.

Upvotes: 3

Tomalak
Tomalak

Reputation: 338406

Try 

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

Upvotes: 2

Related Questions