CODEWITHSUNDEEP
sqlsqlite
alancc
alancc

Reputation: 811

How to Select Specific Rows into a New Table?

I am using SQLite 3. I have a table MyTable, as follows:

Create table mytable (ID as INTEGER, OrderID as INTEGER);

Insert into mytable (ID, OrderID) values (1, 1);
Insert into mytable (ID, OrderID) values (1, 2);
Insert into mytable (ID, OrderID) values (2, 1);
Insert into mytable (ID, OrderID) values (2, 3);
Insert into mytable (ID, OrderID) values (3, 1);

For two rows with same ID but different OrderID, like(1, 1) and (1, 2), we will call them duplicate rows.

Now I need to pick out all duplicate rows and put them into a new table called MyDupTable. For the above sample, MyDupTable should contains:

(1, 1);
(1, 2);
(2, 1);
(2, 3);

So I use the following statement:

Select * into MyDupTable from MyTable group by ID having Count(ID) > 1;

But sqlite gives an error message “near “into”, syntax error”, why?

Thanks

Upvotes: 1

Views: 1018

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32021

You can do it by using sub-query, sub-query will pick all the duplicate id and then by using in pick rest of the columns from table and insert it in to new table

insert into MyDupTable 
select * from mytable where ID in(
    select ID from mytable
     group by ID
    having Count(*) > 1
    )

you can create table by using existing table

CREATE TABLE MyDupTable AS 
   select * from mytable where ID in(
        select ID from mytable
         group by ID
        having Count(*) > 1
        )

your query analysis

Select * into MyDupTable from MyTable group by ID having Count(ID) > 1;

1.you have used group by id but not found any selection column from MyTable , number of column used in selection must used in group by

  1. in sql lite insert table docs

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

I would use INSERT INTO. . . statement with EXISTS :

INSERT INTO MyDupTable (ID, orderid)
     SELECT ID, orderid
     FROM mytable mt
     WHERE EXISTS (SELECT 1 FROM mytable mt1 WHERE mt1.ID = mt.ID AND mt.orderid <> mt1.orderid);

Notes :

  • Always qualify all columns names when you use INSERT INTO Statement.

Upvotes: 0

Related Questions