CODEWITHSUNDEEP
r
haimen
haimen

Reputation: 2015

Using variable in "IN" function of SQL query in R

I am having a variable x which contains 20000 IDs. I want to write a sql query like,

select * from tablename where ID in x;

I am trying to implement this in R where I can get the values only for IDs in x variable. The following is my try,

dbSendQuery(mydb, "select * from tablename where ID in ('$x') ")

I am not getting any error while trying this. But it is returning 0 values.

Next tried using 

sprintf("select * from tablename where ID in %s",x)

But this is creating 20000 individual queries which could prove costly in DB.

Can anybody suggest me a way to write a command, which would loop through IDs in x and save to a Dataframe in R in a single query?

Upvotes: 0

Views: 317

Answers (2)

ChrKoenig
ChrKoenig

Reputation: 973

How about pasting it:

dbSendQuery(mydb, paste("select * from tablename where ID in (", paste(x, collapse = ","), ")"))

Upvotes: 1

cdeterman
cdeterman

Reputation: 19960

You need to have the codes in the actual string. Here is how I would do it with gsub

x <- LETTERS[1:3]

sql <- "select * from tablename where ID in X_ID_CODES "

x_codes <- paste0("('", paste(x, collapse="','"), "')")

sql <- gsub("X_ID_CODES", x_codes, sql)

# see new output
cat(sql)
select * from tablename where ID in ('A','B','C')  


# then submit the query
#dbSendQuery(mydb, sql)

Upvotes: 2

Related Questions