Sqlite adding partial values to table not working

Question

I am trying to programmatically generate tables based on some classes in c#. I have my c# code autodetect changes in the classes (in this case Missile), and update the table if there are any property changes.

The sqlite code I am using is based off of CJH's response to a similar question.

I added the column stackLimit, which seems to be causing problems.

BEGIN TRANSACTION; 
CREATE TEMPORARY TABLE Missile_backup(itemName, description, itemType, itemSubType, itemSubType2, itemID, classID, missileSpeed, flightTime, cost, volume, mass); 
INSERT INTO Missile_backup SELECT itemName, description, itemType, itemSubType, itemSubType2, itemID, classID, missileSpeed, flightTime, cost, volume, mass FROM Missile;
DROP TABLE Missile; 
CREATE TABLE Missile(itemName, description, itemType, itemSubType, itemSubType2, stackLimit, itemID, classID, missileSpeed, flightTime, cost, volume, mass);    
INSERT INTO Missile SELECT itemName, description, itemType, itemSubType, itemSubType2, itemID, classID, missileSpeed, flightTime, cost, volume, mass FROM Missile_backup; 
DROP TABLE Missile_backup; 
COMMIT;

So whenever I create the new version of missile with the additional stackLimit column, and copy over the old information, I get an error:

table Missile has 13 columns but 12 values were supplied

Was his example just incorrect? Or am I missing something? All I want to do is fill the unspecified column here (stackLimit) with null for now.

But in the future it could be several columns, and they would need to be null, so it would need to be automatic.

Thanks

score 2 · Accepted Answer · answered Aug 20 '14 at 20:57

2

If you don't want to insert into all columns, you need to specify a column-list:

INSERT INTO Missile (itemName, description, itemType, itemSubType, itemSubType2, itemID, classID, missileSpeed, flightTime, cost, volume, mass) 
SELECT itemName, description, itemType, itemSubType, itemSubType2, itemID, classID, missileSpeed, flightTime, cost, volume, mass FROM Missile_backup;

Otherwise, how will SQLite know which columns to insert into? It can't rely on the names matching in the general case.

answered Aug 20 '14 at 20:57

Blorgbeard

101,031
48
228
272

Thanks , that makes sense. Two questions: 1. What I have there is a list of all the column names, is that not a column list? 2. Why would you not assume they were the same if only one side is specified? That seems a reasonable assumption to make. – Jeremy Diaz Aug 21 '14 at 18:12
It *could* match the columns by name/alias if possible, I suppose. I guess the answer is that the SQL standard does not specify that behaviour, and SQLite's developers didn't think of it, or didn't think it was a good idea. – Blorgbeard Aug 21 '14 at 19:30

score 0 · Answer 2 · answered Aug 21 '14 at 06:55

0

If you do not specify a column list, you must supply values for all columns:

INSERT INTO Missile SELECT ..., itemSubType2, NULL, itemID, ... FROM Missile_backup;

answered Aug 21 '14 at 06:55

CL.

173,858
17
217
259

Sqlite adding partial values to table not working

2 Answers2