The table consists of id, number and full name. How to make it so that when adding an element with an existing number to the table, update the old element, rather than adding a new one. How to check for the presence of an element with this number?

  • 3
    google sql select where . Then sql update - YuriySPb ♦

1 answer 1

SQLite does not have a separate class for storing boolean. Instead, boolean values ​​can be stored in integers ( integer ) 0 as false and 1 as true .

How to check for the presence of an element with this number?

 SELECT EXISTS( SELECT * FROM Таблица WHERE Номер = 42 LIMIT 1);

The EXISTS statement in SQL returns 1 if the internal SELECT returned at least 1 column, and 0 otherwise.

For better performance, add an index for the Номер column.

How to make it so that when adding an element with an existing number to the table, update the old element and not add a new one?

According to official SQLite3 documentation, ON CONFLICT UPDATE cannot be used, only REPLACE . In the case of a duplicate value of a unique field, the old record will be deleted, and the new one will insert DELETE -> INSERT (новые значения) .

  1. Make the Номер column unique:

     CREATE UNIQUE INDEX Index_номер ON Таблица (Номер) ON CONFLICT REPLACE;

    You can also declare it unique immediately when creating the table:

     CREATE TABLE Таблица ( id INTEGER PRIMARY KEY AUTOINCREMENT, Номер INTEGER NOT NULL, ФИО TEXT NOT NULL, UNIQUE (Номер) ON CONFLICT REPLACE )

  2. Use INSERT OR REPLACE

    ...

  • Tell me, please, how to change this method? If there is no element in the table with such a number, it returns false and otherwise it is true? public boolean Null (int number) {boolean check = true; SQLiteDatabase db = this.getReadableDatabase (); Cursor cursor = db.query (DATABASE_TABLE, new String [] {COLUMN_ID, COLUMN_BUTTON_NUMBER, COLUMN_BUTTON_WIDTH}, COLUMN_BUTTON_NUMBER + "=?" if (cursor! = null) check = false; return check; } - Mihailenko
  • @Mihailenko this comment is similar to an independent question. Therefore, ask a new question - and someone will answer. You can refer to this question in it if you consider it necessary. Comments are usually used to get explanations on the content of the post, to clarify a question or answer. I recommend to get acquainted with the rules of the site . - naXa
  • @Mihailenko Checking if a column exists in an application database in Android - naXa