2

I have created a table in a sqlite3 database from R using the following code:-

con <- DBI::dbConnect(drv = RSQLite::SQLite(),
                      dbname="data/compfleet.db")
s<- sprintf("create table %s(%s, primary key(%s))", "PositionList",
                             paste(names(FinalTable), collapse = ", "),
                             names(FinalTable)[2])
dbGetQuery(con, s)

dbDisconnect(con)

The second column of the table is UID which is the primary key. I then run a script to update the data in the table. The updated data could contain the same UID which already exists in the table. I don't want these existing records to be updated and just want the new records(with new UID values) to be appended to this database. The code I am using is:-

DBI::dbWriteTable(con, "PositionList", FinalTable, append=TRUE, row.names=FALSE, overwite=FALSE)

Which returns an error:

Error in result_bind(res@ptr, params) : 
  UNIQUE constraint failed: PositionList.UID

How can I achieve the task of appending only the new UID values without changing the existing UID values even if they appear when I run my updation script?

Dhiraj
  • 1,650
  • 1
  • 18
  • 44

2 Answers2

1

You can query the existing UIDs (as a one-column data frame) and remove corresponding rows from the table you want to insert.

uid_df <- dbGetQuery(con, "SELECT UID FROM PositionList")
dbWriteTable(con, "PositionList", FinalTable[!(FinalTable$UID %in% uid_df[[1]]), ], ...)
krlmlr
  • 25,056
  • 14
  • 120
  • 217
0

When you are going to insert data,first get the data from database by using UID.If data is exist nothing to do else insert new data with new UID.Duplicate Primary Key (UID) recard is not exist ,so it show the error.

Amit
  • 1