R: Updating SQL table loop using R

Question

I am using the RODBC package on R which allows me to connect to SQL using R.

As an example to my problem, I have a table [Sales] within SQL with 3 Columns (Alpha, Beta, BetaDistribution).

1.50,77,x

2.99,53,x

4.50,122,x

Note that the 3rd column (BetaDistribution) is not populated, and this needs to be populated using a Statistical R Function.

---

I have assigned my table to the variable SELECT

select <- sqlQuery(dbhandle, 'select * from dbo.sales')

how to I run a loop to update my sql table so that the BetaDistribution column is updated with the calculated Beta Distribution - pbeta(alpha,beta)

Answer 1

Something like this. Basically you make a temp table and then update the existing table. There's a reasonable chance you need to tweak that update statement since I, obviously, can't test it.

select$BetaDistribution<-yourfunc(x,y)
sqlSave(dbhandle, select, tablename="dbo.salestemp", rownames=FALSE,varTypes=list(Alpha="decimal(10,10)", Beta="decimal(10,10)", BetaDistribution="decimal(10,10)"))
sqlQuery(dbhandle, "update dbo.sales 
                    set sales.BetaDistribution=salestemp.BetaDistribution 
                    from dbo.sales
                    inner join
                    salestemp
                    on
                    sales.Alpha=salestemp.Alpha and 
                    sales.Beta=salestemp.Beta")
sqlQuery(dbhandle, "drop table salestemp")