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

Question

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?

cdeterman · Accepted Answer · 2015-11-17 19:53:35Z

2

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)

answered Nov 17, 2015 at 19:53

cdeterman

20k7 gold badges82 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

haimen Over a year ago

Both the codes are working fine. Since that was in a single line and easier way, I accepted that. Thanks a lot !

cdeterman Over a year ago

@haimen whatever your preference. Keep this in mind, however, if you begin to develop more complex queries that need to be dynamic. Those single line statements can get pretty unwieldy.

ChrKoenig · Accepted Answer · 2015-11-17 20:22:48Z

1

How about pasting it:

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

answered Nov 17, 2015 at 20:22

ChrKoenig

9831 gold badge9 silver badges24 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related