Sql query based on R values in datatable

Question

I have few blank values in one of my columns in dataset. I need to make sql query to database with only this few id (86) that contain missing value.

I have in mind something like that (not just paste id to in statement):

SELECT x.id,
x.sent 
FROM x
WHERE x.id IN [my R vector with id]

Answer 1

glue_sql will expand vectors into a comma separated list if you use * after the variable name in the glue expression.

glue::glue_sql("
  SELECT x.id,
  x.sent 
  FROM x
  WHERE x.id IN ({idVector*})
", .con = con)

con is a DBI connection to your database

Answer 2

Conjecture.

Real data is in #sourcemt (a temp table, the "#" is purely local for my database, ignore it).

sourcemt <- mt <- transform(mtcars, id = seq_len(nrow(mtcars)))
DBI::dbWriteTable(con, "#sourcemt", sourcemt)

mt <- head(mt)
mt$cyl[c(1,3,4)] <- NA
mt
#                    mpg cyl disp  hp drat    wt  qsec vs am gear carb id
# Mazda RX4         21.0  NA  160 110 3.90 2.620 16.46  0  1    4    4  1
# Mazda RX4 Wag     21.0   6  160 110 3.90 2.875 17.02  0  1    4    4  2
# Datsun 710        22.8  NA  108  93 3.85 2.320 18.61  1  1    4    1  3
# Hornet 4 Drive    21.4  NA  258 110 3.08 3.215 19.44  1  0    3    1  4
# Hornet Sportabout 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2  5
# Valiant           18.1   6  225 105 2.76 3.460 20.22  1  0    3    1  6

We want to fix the missing cyl values.

miss_ids <- mt$id[is.na(mt$cyl)]
miss_ids
# [1] 1 3 4
(qmarks <- paste(rep("?", length(miss_ids)), collapse = ","))
# [1] "?,?,?"
fixmt <- DBI::dbGetQuery(con, sprintf("select id, cyl from #sourcemt where id in (%s)", qmarks), params = miss_ids)
fixmt
#   id cyl
# 1  1   6
# 2  3   4
# 3  4   6

(FYI, the qmarks parts are using parameter-binding for safe querying of data without paste-ing or sprintf-ing the data into the query. See parameterized queries for good discussions about this.)

From here, we just need to merge them and coalesce the missing values back in. (Both methods below should capture the output back into mt.)

dplyr

library(dplyr)
mt %>%
  left_join(fixmt, by = "id", suffix = c("", ".y")) %>%
  mutate(cyl = coalesce(cyl, cyl.y)) %>%
  select(-cyl.y)
#    mpg cyl disp  hp drat    wt  qsec vs am gear carb id
# 1 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4  1
# 2 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4  2
# 3 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1  3
# 4 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1  4
# 5 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2  5
# 6 18.1   6  225 105 2.76 3.460 20.22  1  0    3    1  6

base R

merge(mt, fixmt, by = "id", all.x = TRUE, suffixes = c("", ".y")) |>
  transform(cyl = ifelse(is.na(cyl), cyl.y, cyl), cyl.y = NULL)
#   id  mpg cyl disp  hp drat    wt  qsec vs am gear carb
# 1  1 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
# 2  2 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
# 3  3 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1
# 4  4 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
# 5  5 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
# 6  6 18.1   6  225 105 2.76 3.460 20.22  1  0    3    1