R/updateSQL.R

In elo2zero/oddsandsods: Miscellanous functions

#' Custom SQL Query on existing connection
#'
#' Custom SQL Query on existing connection
#' @param query SQL query to be processed
#' @param return_query if TRUE function returns only character element with custom query
#' @param ... arguments passed to query
#' @examples
#' iris$id = 1:nrow(iris)
#' sqlInsert(iris, table_name = "iris")
#' df = iris[1:5,]
#' df[1,1] <- NA
#' df[4,5] <- NA
#' df[2,2] <- ""
#' sqlUpdate(iris, table_name = "iris", by="id", addIndex=F, return_query = T)
#' @export
sqlUpdate <- function(df_new, table_name, by="id", addIndex = T, return_query=F){
  require(oddsandsods); require(car);require(magrittr)
  # df_new = iris[1:5,]; df[1,1] <- NA; df[4,5] <- NA; df[2,2] <- "" ;df$id = 1:nrow(df)
  colnames(df_new) %<>% gsub("[[:punct:]]","_",x=.)
  df_new[df_new==""] <- NA
  dbSendQuery(con, "DROP TABLE IF EXISTS tempTable")
  df_new %>% oddsandsods:::sqlCreateTable(table_name = "tempTable")
  df_new %>% sqlInsert("tempTable")
  if(addIndex)
    for(i in 1:length(by))
      sprintf("ALTER TABLE %s ADD INDEX (%s)", "tempTable", by[i])

  fields_to_match <-
    paste0(paste0("old.",by)," = ",paste0("new.",by)) %>%
    paste(collapse=" and ")

  fields_to_update   <- setdiff(colnames(df_new), by)
  fields_to_update   <- paste0(
    paste0("  old.",fields_to_update)," = ",
    paste0("new.",fields_to_update)
  ) %>%
    paste(collapse=",\n")

update_cmd <- sprintf({"
UPDATE %1$s old
INNER JOIN tempTable new
ON %2$s
SET
%3$s,
  old.updated_at = CURRENT_TIMESTAMP;"}, table_name, fields_to_match, fields_to_update)

  if(return_query) return(update_cmd)
  dbSendQuery(con, update_cmd); dbClearResult(dbListResults(con)[[1]])
}