SQLite: how do I fix syntax on INSERT INTO?

Question

I have a column CARDNUM that I am trying to increment by +1 in a Table called TOTALCOUNT. App crashed with syntax error near "SET"

This is my INSERT statement:

db.execSQL("INSERT INTO "+ DBContract.DBEntry.TABLE_NAME_TOTALCOUNT+" SET "+ DBContract.DBEntry.COLUMN_NAME_CARDNUM + "="+ DBContract.DBEntry.COLUMN_NAME_CARDNUM+"+1");

What am I not getting right?

private static final String SQL_CREATE_CARDNUM =
            "CREATE TABLE IF NOT EXISTS "+ DBContract.DBEntry.TABLE_NAME_TOTALCOUNT +
            "( "+ DBContract.DBEntry.COLUMN_NAME_COUNTID +
            " INTEGER PRIMARY KEY UNIQUE , "+
            DBContract.DBEntry.COLUMN_NAME_CARDNUM +
            " INTEGER DEFAULT 1);";


// Push the database data to the RecyclerView
public List<UserData> getDataFromDB(){
    List<UserData> modelList = new ArrayList<>();        
    String query = "SELECT *,(SELECT " + DBContract.DBEntry.COLUMN_NAME_CARDNUM +
            " from " + DBContract.DBEntry.TABLE_NAME_TOTALCOUNT +") from "
            + DBContract.DBEntry.TABLE_NAME_USERINPUTS +";";

    SQLiteDatabase db = this.getReadableDatabase();

    db.beginTransaction();

    Cursor cursor = db.rawQuery(query, null);

        try {
            if (cursor.moveToFirst()) {
                do {
                    UserData userData = new UserData();
                    userData.setTodo(cursor.getString(1));
                    userData.setNote1(cursor.getString(2));
                    userData.setNote2(cursor.getString(3));
                    userData.setDuedate(cursor.getString(4));
                    userData.setDuetime(cursor.getString(5));
                    userData.setTimestamp(cursor.getLong6));
                    userData.setCardnum(cursor.getInt(7));

                    modelList.add(0, userData);
                    } while (cursor.moveToNext());
            }
        } finally {
              if(cursor !=null && !cursor.isClosed()){
              cursor.close();
              }
          }
    db.setTransactionSuccessful();
    db.endTransaction();
    return modelList;
}

The Logcat for CARDNUM shows "(SELECT cardnum from totalcount)=0.

You can't use "set" in INSERT as I know. Its there in UPDATE. Please correct me if I am wrong. — Raghavendra, May 11 '16 at 05:30
Refer this : http://www.w3schools.com/sql/sql_insert.asp.. Your insert query is wrong..!! — Janki Gadhiya, May 11 '16 at 05:43
@janki gadhiya Thank you I will check it out...but please let me know what specifically is wrong! — AJW, May 11 '16 at 05:45
you have previously inserted any data and you want to Update it or ?? you want a fresh record to be inserted to the database ?? — Janki Gadhiya, May 11 '16 at 05:47
ok share your Table structure and tell me what is `DBContract.DBEntry.COLUMN_NAME_CARDNUM`.?? — Janki Gadhiya, May 11 '16 at 05:48
DBContract.DBEntry.COLUMN_NAME_CARDNUM is a column. It is very simple. The first row should default to 1. Then each row created with INSERT thereafter is +1. So the row from the start would be 1,2,3,4,5 after 4 INSERTS. — AJW, May 11 '16 at 05:54

ads · Answer 1 · 2016-05-11T07:17:35.210

1

You have an incorrect syntax for INSERT.

Refer to the following:

http://www.sqlitetutorial.net/sqlite-insert http://www.techonthenet.com/sqlite/insert.php

For example:

INSERT INTO table1 (
 column1,
 column2 ,..)
VALUES
 (
 value1,
 value2 ,...);

edited May 11 '16 at 07:17

answered May 11 '16 at 05:27

ads

1,703
2
18
35

score 0 · Answer 2 · answered May 11 '16 at 05:31

0

try this

db.execSQL("UPDATE "+ DBContract.DBEntry.TABLE_NAME_TOTALCOUNT+" SET "+ DBContract.DBEntry.COLUMN_NAME_CARDNUM + "="+ DBContract.DBEntry.COLUMN_NAME_CARDNUM+"+1");

It is recommended to set id using WHERE to update particular row.

answered May 11 '16 at 05:31

Hello World

2,764
1
11
23

I want to use the INSERT to add a new row to the column. I thought UPDATE should only be used for editing/updating existing row data. Please advise. – AJW May 11 '16 at 05:37
How you want to increment `CARDNUM` by 1 if row is not already exists? – Hello World May 11 '16 at 05:38
The default value of CARDNUM is set to 1 when the table is created: "...COLUMN_NAME_CARDNUM + " INTEGER DEFAULT 1)" – AJW May 11 '16 at 05:40
In that case you have to use `INSERT` query only and set `2` as a value of `CARDNUM` column – Hello World May 11 '16 at 05:43
Why 2? And where do you set the value? I thought the primary key was column zero and the CARDNUM column is 1, correct? – AJW May 11 '16 at 06:12
if you want to add the value of this column sequentially, add `autoincrement` to property to this column. – Hello World May 11 '16 at 06:23

score 0 · Answer 3 · answered May 11 '16 at 06:08

You are trying to increment a column using INSERT which can not be done. But if you still want to use INSERT statement then you can not use SET in INSERT. An INSERT operation should look like this:

INSERT INTO TABLE_NAME (cloumn1_name, column2_name, column3_name) VALUES (column1_value,cloumn2_value,column3_value)

You can nou update a value via INSERT statement. If you want to update a value you must use UPDATE statement. In that case an UPDATE statement looks like this:

UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition];

SQLite: how do I fix syntax on INSERT INTO?

3 Answers3