I have a sql connection, and i get information to work with algorithms in r. So in FTSE100 i get the result of my algorithm in a data frame in R like this.
canlSQL<-odbcConnect("Myconnection")
DATE AMSTEOE FTSE100 FRCAC40 DAXINDX
1 03/01/1994 190.76 2 2290.56 2267.98
2 04/01/1994 190.99 3 2274.34 2253.58
3 05/01/1994 191.68 4 2249.55 2233.41
4 06/01/1994 192.21 6 2275.06 2220.22
5 07/01/1994 192.41 3 2307.55 2211.64
6 10/01/1994 192.59 2 2317.25 2233.79
.
.
.
.
1000 10/01/1999 200.59 5 2317.25 2233.79
In SQL i have a table with the same structure but more columns, I want update the FTSE_A column in SQL with the information of FTSE100 column of the data frame in R.
DATE.REF AMST_A FTSE_A FRCA_A DAX_A COL.1 COL.2 COL.3
1 03/01/1994 190.76 NA 2290.56 2267.98 NA NA NA
2 04/01/1994 190.99 NA 2274.34 2253.58 NA NA NA
3 05/01/1994 191.68 NA 2249.55 2233.41 NA NA NA
4 06/01/1994 192.21 NA 2275.06 2220.22 NA NA NA
5 07/01/1994 192.41 NA 2307.55 2211.64 NA NA NA
6 10/01/1994 192.59 NA 2317.25 2233.79 NA NA NA
.
.
.
.
1000 10/01/1999 200.59 NA 2317.25 2233.79 NA NA NA
So i would like to have the next result:
DATE.REF AMST_A FTSE_A FRCA_A DAX_A COL.1 COL.2 COL.3
1 03/01/1994 190.76 2 2290.56 2267.98 NA NA NA
2 04/01/1994 190.99 3 2274.34 2253.58 NA NA NA
3 05/01/1994 191.68 4 2249.55 2233.41 NA NA NA
4 06/01/1994 192.21 6 2275.06 2220.22 NA NA NA
5 07/01/1994 192.41 3 2307.55 2211.64 NA NA NA
6 10/01/1994 192.59 2 2317.25 2233.79 NA NA NA
.
.
.
.
1000 10/01/1999 200.59 5 2317.25 2233.79 NA NA NA
I have tried to do the next in R:
for(i in 1:1000){
sqlQuery(canlSQL,paste0("UPDATE MYTABLE SET FTSE_A= '",DATAFRAME$FTSE100[i],"'"))
}
I don't get an error when i run the code, but in the SQL results i get the next:
DATE.REF AMST_A FTSE_A FRCA_A DAX_A COL.1 COL.2 COL.3
1 03/01/1994 190.76 5 2290.56 2267.98 NA NA NA
2 04/01/1994 190.99 5 2274.34 2253.58 NA NA NA
3 05/01/1994 191.68 5 2249.55 2233.41 NA NA NA
4 06/01/1994 192.21 5 2275.06 2220.22 NA NA NA
5 07/01/1994 192.41 5 2307.55 2211.64 NA NA NA
6 10/01/1994 192.59 5 2317.25 2233.79 NA NA NA
.
.
.
.
1000 10/01/1999 200.59 5 2317.25 2233.79 NA NA NA
I don't need the where condition, because if I use the WHERE condition i could get a duplicated FTSE_A result, the columns don't have a unique value to pass the unique result of FTSE100. Actually when i use the WHERE condition I get values in wrong places.
I need to update the FTSE_A column in the SQL table, some one know how i can update it with the results obtained in R, but just the FTSE_A column?