SQL delete query not executing using sqldf in R

Question

I have a table - "cla_retail" - in sql that has 20,000+ rows. I have read the tables in R using dbConnect and stored it in a dataframe called "cla_retail_df" I am trying to execute a DELETE query using sqldf on dataframe abc. This delete query works fine when I execute it in mySQL but returns 0 results when I execute it in R.

I also tried using dplyr but I got confused with the many "not" in the sql query. I have initialised the other dataframes

sales_vou_main_appr,
issue_to_karigar_approval_main ,
issue_to_hallmark_main ,
sample_issue_to_karigar_main

Here is what I have tried in R and throws 0 rows in abc_v2:

abc <- cla_retail_df

abc_v2<- sqldf("delete from abc where status<>'N' and barcode not in(select barcode from sales_vou_main_appr where ret_status='0') and barcode not in(select barcode from issue_to_karigar_approval_main where ret_status='0') and barcode not in(select barcode from issue_to_hallmark_main where rec_status='0') and barcode not in(select barcode from sample_issue_to_karigar_main where rec_status='0')")

This is mostly a dupe of stackoverflow.com/q/61373594/3358272 (and therefore sqldf FAQ 8 is relevant), though you're using DELETE instead of UPDATE. The premise is (I believe) the same: sqldf doesn't delete in-place, and it never returns automatically. If you want abc_vw to be the contents of cla_retail_df without the rows, then abc_v2 <- sqldf(c("delete from abc where ...", "select * from abc")). — r2evans
– r2evans, Commented Jan 10, 2023 at 14:45

r2evans · Accepted Answer · 2023-01-10 14:56:35Z

Try this in dplyr:

lapply(list(sales_vou_main_appr, issue_to_karigar_approval_main,
            issue_to_hallmark_main, sample_issue_to_karigar_main),
       function(Z) filter(Z, ret_status == "0") %>% select(barcode)) %>%
  bind_rows() %>%
  anti_join(cla_retail_df, by = "barcode") %>%
  filter(status != "N")

As for using DELETE in sqldf, realize that sqldf doesn't return data (on updates/deletes) by default for safety reasons (see the sqldf FAQ 8, scroll to question 8), you have to explicitly request it with a second query. Try:

abc_v2 <- sqldf(c("delete from abc where ...",
                  "select * from abc"))

Realize that "delete from abc where ..." doesn't delete anything from the data.frame object abc, it deletes it from the (temporary?) table named abc in the in-memory SQLite database it uses by default (assuming you are using the SQLite engine of sqldf).

Collectives™ on Stack Overflow

SQL delete query not executing using sqldf in R

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related