[R] Problems with modifying data in a MySQL database

[Duncan Murdoch]

On 1/8/2008 7:11 AM, Marcus Wurzer wrote:
> Using the package /RMySQL/ I have established a connection ('con1') to a MySQL-Database called 'mc'.
> This database contains a table called 'mc_2000' which has 200000 observations on several
> variables. One of these variables is called 'B5' (type 'double').
> When I generate a numeric vector in R called 'B6' that has the same length as 'B5': How 
> can I replace the values of 'B5' with the values of 'B6'? I tried
> 
> dbGetQuery(con1,
> "UPDATE mc_2000 SET B5 = B6", B6)
> 
> but this doesn't work. The same applies to:
> 
> dbGetQuery(con1,
> "REPLACE mc_2000(B5) VALUES(B6)", B6)
> 
> Any help would be appreciated very much.

I haven't used RMySQL specifically, but in general the DBI based 
database packages don't handle parameters they way you want.  You need 
to construct an SQL query as a string (using paste(), etc.) and then use 
dbGetQuery to send it to the database.

However, constructing a query to do what you want is tricky.  You need 
to specify a key so it knows which entry of B6 goes with which entry in 
the database, etc.  It might be easiest to read the whole table into 
memory using dbReadTable, modify it, and write it out with dbWriteTable.

Duncan Murdoch