Re: [R] Problems with modifying data in a MySQL database

From: Marcus Wurzer <mwurzer_at_wu-wien.ac.at>
Date: Thu, 10 Jan 2008 12:01:39 +0100

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
>
>



R-help_at_r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code. Received on Thu 10 Jan 2008 - 11:06:31 GMT

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Thu 10 Jan 2008 - 12:30:05 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.

list of date sections of archive