When I try to execute following query:
SELECT id_subscriber
INTO newsletter_to_send
FROM subscribers
I get an error:
#1327 - Undeclared variable: newsletter_to_send
What is wrong with that query ?
11 Answers
INSERT ... SELECT
http://dev.mysql.com/doc/refman/5.1/en/insert-select.html
INSERT INTO newsletter_to_send
SELECT id_subscriber FROM subscribers
PS: are you sure you don't need in WHERE clause?
7 Comments
Praveen Kumar
For using insert into we need to create the table first , in this case if table newsletter_to_send doesn't exist , there will be error!
zerkms
@Praveen Kumar: it's obvious that you need to have a server, with mysql installed and table created, isn't it?
Praveen Kumar
Actually , I mentioned it even more because for select ...into syntax table will be created if it doesn't exist , however with insert into it isn't so.
zerkms
@Praveen Kumar: "
select ...into syntax" --- any reference in documentation? I'm not aware of such syntax.
sam
would you mind to help me with issue here please stackoverflow.com/questions/43987326/…
|
MySQL does not support the SELECT ... INTO ... syntax.
You have to use the INSERT INTO ... SELECT .. syntax to accomplish there.
Read more here.. http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
Comments
I think you can follow my given way and hopefully you will be able to fix your problem.
At first use this sql command to create a new table where you want to take backup
CREATE TABLE destination_table_name LIKE source_table_name;
After then you can use this command to copy those data
INSERT INTO destination_table_name
SELECT * FROM source_table_name;
If you already have previous data in your Destination table , Firstly you can use this command
TRUNCATE TABLE destination_table_name;
Thanks By Md. Maruf Hossain
Comments
CREATE TABLE table_name
AS
SELECT ...(your select)
1 Comment
salvob
well... this is way better, especially if you don't know (or you don't want to write ) all the columns' names
MySQL does not support SELECT INTO [table]. It only supports SELECT INTO [variable] and can only do this one variable at a time.
What you can do, however is use the CREATE TABLE syntax with a SELECT like so:
CREATE TABLE bar ([column list]) SELECT * FROM foo;
2 Comments
ejectamenta
This example is good and allows mysql to do the equivalent of SELECT ... INTO ... by auto creating a table
El Gucs
At least for the latest version of MySQL you can assign multiple values to multiple variables in one
SELECT [values] INTO [variables] statement. Check dev.mysql.com/doc/refman/5.7/en/select-into.htmlI tried working on this just now and this works for me:
CREATE TABLE New_Table_name
SELECT * FROM Original_Table_name;
Comments
MySQL Server doesn't support the SELECT ... INTO TABLE Sybase SQL extension. Instead, MySQL Server supports the INSERT INTO ... SELECT standard SQL syntax, which is basically the same thing. See Section 12.2.5.1, “INSERT ... SELECT Syntax”.
Ref:- this
Comments
mysql don't support SELECT ... INTO ... syntax,
if it's a new table, use CREATE TABLE ... SELECT ... syntax.
example:
CREATE TABLE artists_and_works
SELECT artist.name, COUNT(work.artist_id) AS number_of_works
FROM artist LEFT JOIN work ON artist.id = work.artist_id
GROUP BY artist.id;
read more here create-table-select
Comments
If you want to create a new table as a Duplicate table.
CREATE TABLE WorkerClone LIKE Worker; //only structure will show no data
CREATE TABLE WorkerClone Select * from Worker; //DUPLICATE table created with all details.
Comments
You can do this without CREATE TABLE + INSERT INTO + DROP TABLE Like this:
SELECT @newsletter_to_send := id_subscriber
FROM subscribers WHERE your_condition = your_value;
Comments
MySQL does not support SELECT INTO [table]. It only supports SELECT INTO [variable] and can only do this one variable at a time.
What you can do, however is use the CREATE TABLE syntax with a SELECT like so:
select ... intostatements. See: dev.mysql.com/doc/refman/8.0/en/select-into.html