INSERT OR REPLACE only works with 1 row in database

Viewed 55

INSERT OR REPLACE works when there is only one row in the table and it happens to be the one that is replaced. However, when there are multiple rows in the table, INSERT OR REPLACE just inserts and creates a duplicate!

For example, if I create an entry with eventId = '123'. And then try to write another entry with eventId = '123' and roomnumber equal to something different than the first entry, INSERT OR REPLACE will work. However, if there are more than one rows in the table it will create a duplicate. Here is my code:

String createSql = "CREATE TABLE IF NOT EXISTS events (\n"
            + " eventId VARCHAR(20) PRIMARY KEY ON CONFLICT REPLACE,\n"
            + " roomNumber VARCHAR(20) NOT NULL,\n"
            + " startTime TIMESTAMP NOT NULL,\n"
            + " endTime TIMESTAMP NOT NULL,\n"
            + " name VARCHAR(250),\n"
            + " isVIP BOOLEAN NOT NULL,\n"
            + " capacity INTEGER(20) NOT NULL, \n"
            + " UNIQUE (eventId) \n"
            + ");";

String sql = "INSERT OR REPLACE INTO events (eventId, roomNumber, startTime, endTime, capacity, name, isVIP)" + " Values('"+event.getID()+"', '"+event.getRoomNumber()+"', '"+event.getStartTime()+"', '"+event.getEndTime()+"', '"+event.getCapacity()+"', '"+event.getName()+"', '"+event.getVIPStatus()+"')";
1 Answers
Related