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