0

I need to write an Update SQL query via R.

Assume I have a table in R:

data <- data.frame(col1=c(1, 2, 4), col2=c(5, 4, 7))

How to write SQL update query with two in operators

I tried the following way

update tabl1
set column_tO-be_updated = 'value'
where col_for_fiter1 in data$col1
and col_for_fiter2 in data$col2

Hovewer I didn't succed.

Please, help.

Davit
  • 97
  • 7
  • Specifically https://stackoverflow.com/a/70572822/3358272, though I know the other answerer does not share my concern with `paste`-ing or `sprintf`-ing data into a query. – r2evans Jan 17 '22 at 15:45
  • Please post the `DDL` and sample data for the database tables and the expected result. I guess you'll have to upload your df in the database as a `temp` table and then use something like `... where (col1) in (select col1 from temp) and (col1) in (select col2 from temp) ...`,. The usage of `in list`as proposed in other comment is also possible if you are below 1000 values (Oracle limitation - independent of the usage of bind variables or value concatenation). – Marmite Bomber Jan 17 '22 at 15:56
  • @MarmiteBomber I've already tried this version. Orcale returned ORA-00942. – Davit Jan 17 '22 at 16:03
  • `ORA-00942: table or view does not exist` well of course the temp table *must* exists. If you use the `temp` table approach, you question has **nothing** to do with `r`-> edit the question to re-open it. It is `Oracle UPDATE` using two tables topic only – Marmite Bomber Jan 17 '22 at 16:10
  • @MarmiteBomber, I don't see how this is not related to [tag:r]: the OP is attempting to update a database table (existing or not) using a SQL query that references columns of a `data.frame`. Frankly, this could be the dupe as marked, it could also be an "upsert" issue, in which case https://stackoverflow.com/a/59957643/3358272 might be a better fit (though the upsert language might need to change for Oracle, I don't have one running to test, perhaps see https://stackoverflow.com/a/237328/3358272.) – r2evans Jan 18 '22 at 13:27

0 Answers0