[R] Problems with modifying data in a MySQL database

[Marcus Wurzer]

Thank you!

Duncan Murdoch schrieb:
> 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
>
>