Insert two commands (INSERT and UPDATE) into the same instruction in Sqlite, via R Language

Asked

Viewed 329 times

1

Hello, everybody

I have a problem in knowing how to insert two commands (INSERT and UPDATE) in the same instruction in Sqlite, via R Language.

I don’t know if this is impossible due to Sqlite or R’s DBI package deficiency.

Follow image, with reproducible example, about what I’m trying to do:

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")

DBI::dbWriteTable(con, "cars_a", head(cars, 3))

DBI::dbWriteTable(con, "cars_b", head(cars, 3))

DBI::dbExecute(con, 
    "INSERT INTO cars_a
     VALUES (10, 10 );

     UPDATE cars_b
     SET dist = 400
     WHERE speed = 7;"

DBI::dbReadTable(con, "cars_a")

DBI::dbReadTable(con, "cars_b")

NOTE: Although the tables are named as "cars_a" and "cars_b" interpret that they are related. In my case, as I am working with a Web Scraping, INSERT has the purpose of aggregating data in the table with the data scraped, and UPDATE updates the request table.

After trying several ways, I could only execute the first SQL statement. As shown below:

inserir a descrição da imagem aqui

sql r sqlite

  • NOTE: I need to have both commands in the same SQL statement to ensure that INSERT and UPDATE will be done. The solution I’m adopting today is to put the commands in separate instructions, but it’s not the idea when it comes to performance and information security bias.

  • George, edit the question, add that remark. Also, add your code, not image, so anyone who wants to simulate, test, facilitate, and better organized!

  • Please edit the question with copy/Paste of the instructions, not as images. With the code we can copy to an R session and test, with images we can’t, we have to enter everything again via keyboard.

1 answer

3


Put the two instructions to be executed in the same query probably possible, however this will not guarantee that the two operations will be carried out. For even if it is sent in the same query, it will execute only one command at a time.

How to ensure that two operations are carried out in the bank?

For this you can use the Transaction (in English, transaction) which is used to ensure data integrity and reliability.

Like the transaction works?

The transaction has three commands:

BEGIN: Initiates the transaction.

COMIT: Confirms the bank’s operations.

ROLLBACK: Undoes the operations carried out.

How to use?

See an example, taken from this website

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbBegin(con) # inicia a transação
withdrawal <- 5000

#realiza duas operações de update
dbExecute(con, "UPDATE cash SET amount = amount + ?", list(withdrawal)) 
dbExecute(con, "UPDATE account SET amount = amount - ?", list(withdrawal))

# realiza uma verificação
if (dbReadTable(con, "account")$amount >= 0) {
  dbCommit(con)
} else {
  dbRollback(con)
}

On the check performed, if you pass in your condition it will perform dbCommit(con) confirmed both UPDATE which he had previously done, if the validation condition is false, he will perform a dbRollback(con) undoing the changes in UPDATE.

You can read more about Sqlite with Transaction here and here.

Browser other questions tagged sql r sqlite

You are not signed in. Login or sign up in order to post.