SELECT * INTO with a subquery

Question

i'm quite a newbie with MySQL and i'm trying to create a table using a select * into. My problem is that i'm using a subquery and i don't understand the right sintax for the query. Morover, with this query i'll display all the record that i want to copy:

    select trasco_titolarita.* 
    from trasco_titolarita
    inner join (
                select max(id) as maxID, soggetto_id 
                from trasco_titolarita group by soggetto_id
               ) maxID
    on maxID.maxID = trasco_titolarita.id

as above, this query displays all the record that i'm interested in. The goal i want to achieve, is to copy all these records into another new table, so i was trying this:

select * into newtable from
(
select trasco_titolarita.* 
from trasco_titolarita
    inner join (
                select max(id) as maxID, soggetto_id 
                from trasco_titolarita group by soggetto_id
               ) maxID
    on maxID.maxID = trasco_titolarita.id

)

but this actually doesn't work, for a reasong i think is that the first select in the subquery is just a display. The error i get is "Incorrect syntax near ')' " Can someone give me some tips?

Try to list all fields instead of * and list them within inner query. — Dmitry Surin, Jul 25 '18 at 10:15

score 0 · Answer 1 · answered Jul 25 '18 at 10:14

0

I'm pretty sure that MySQL Server doesn't support the SELECT ... INTO syntax

you could instead use CREATE TABLE new_tbl SELECT * FROM orig_tbl WHERE .... syntax

for further information read https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-select-into-table.html

answered Jul 25 '18 at 10:14

Clint

973
7
18

1

I does support the select ... into ... syntax, but that syntax has a different purpose in MySQL. You can use that syntax to export data to file or fetch data into variables. – Shadow Jul 25 '18 at 10:30

score 0 · Accepted Answer · answered Jul 25 '18 at 10:15

0

You need to change select * into by insert into. https://dev.mysql.com/doc/refman/8.0/en/insert-select.html

Assuming newtable has similar columns as existing trasco_titolarita. The query that will work for you is ::

insert into newtable
select trasco_titolarita.* 
from trasco_titolarita
    inner join (
                select max(id) as maxID, soggetto_id 
                from trasco_titolarita group by soggetto_id
               ) maxID
    on maxID.maxID = trasco_titolarita.id

answered Jul 25 '18 at 10:15

skelwa

575
1
7
17

1

such an obvious edit. I was entirely focused on the select into, since i've almost use it for copy original table in tmp_tables. Thank you! – Serphentelm Jul 25 '18 at 10:19
You can [accept answers that solve your problem](https://stackoverflow.com/help/someone-answers) - it will help others searching for answers. :) – skelwa Jul 25 '18 at 10:25
`SELECT INTO newtable` creates the table, `INSERT INTO newtable` requires that the table already exist. – Barmar Jul 25 '18 at 10:26

SELECT * INTO with a subquery

2 Answers2