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

From: Duncan Murdoch <murdoch_at_stats.uwo.ca>
Date: Tue, 08 Jan 2008 08:38:31 -0500

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 Tue 08 Jan 2008 - 13:43:00 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 - 11: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