Kurt Leadley Kurt Leadley - 3 months ago 11
PHP Question

ERROR: UNIQUE constraint failed....but not when I use a SELECT * Statement (SQLite)

I'm having a problem. I have a table in which I want to move its data into another table. When I do :

INSERT INTO rosters SELECT * FROM rosters_test


All of the rows enter into the database. The problem is that the columns are out of order for the new data entered (the new data has
id
where
name
should be etc), therefore making the data incorrect and useless.

ohcrap
I have tried to be more specific about the insert statement, but that is where the
ERROR: UNIQUE constraint failed
occurs. I have a dual key on the
rosters
table which is the
id
and the
season
. I am trying to insert the 20142015 roster from the
rosters_test
table into the
rosters
table where the 20152016 and 20162017 rosters reside.


Every row in the
roster_test
table is
20142015
for the
season
and I checked all 27 rows for repeat ids and there are none. These are the queries I tried:

INSERT INTO 'rosters'
SELECT 'number' AS 'number', 'name' AS 'name' , 'height' AS 'height', 'weight' AS 'weight', 'birthplace' AS 'birthplace', 'birthdate' AS 'birthdate', 'position' AS 'position', 'id' AS 'id', 'age' AS 'age', 'season' AS 'season', 'imageURL' AS 'imageURL'
FROM 'rosters_test'

INSERT INTO 'rosters' (number, name, height, weight, birthplace, birthdate ,position ,id ,age ,season, imageURL)
SELECT 'number', 'name' , 'height', 'weight', 'birthplace', 'birthdate', 'position', 'id', 'age' , 'season', 'imageURL'
FROM 'rosters_test'


I also tried things such as rosters_test.number AS rosters.number as well. Nothing is working: Here is the schema of the two tables:


CREATE TABLE 'rosters_test' ('number' INTEGER, 'name' TEXT, 'height' INTEGER, 'weight' INTEGER, 'birthplace' TEXT, 'birthdate' TEXT, 'position' TEXT, 'id' INTEGER, 'age' INTEGER, 'season' INTEGER, 'imageURL' TEXT)


I had a dual key on the
rosters_test
table at one point too.

CREATE TABLE 'rosters' ('position' TEXT, 'id' INTEGER, 'weight' INTEGER, 'height' TEXT, 'imageURL' TEXT, 'birthplace' TEXT, 'age' INTEGER, 'name' TEXT, 'birthdate' TEXT, 'number' INTEGER, 'season' INTEGER, PRIMARY KEY('id','season'))

Answer

Use INSERT INTO...SELECT and explicity specify the columns (and their order) which you want for the rosters table:

INSERT INTO rosters (position, id, weight, height, imageURL, birthplace, age, name,
                     birthdate, number, season)
SELECT position, id, weight, height, imageURL, birthplace, age, name, birthdate,
       number, season
FROM rosters_test
Comments