4

I wrote a data.frame using

dbWriteTable(con, name='db_all', df, overwrite=T, row.names=F)

sucessfully to MySQL using RMySQL. Now I have a second data frame which has a similar structure and try to use 

dbWriteTable(con,name='db_all',df1,append=T,row.names=F,overwrite=F)

which gives me

Error in .local(conn, statement, ...) : could not run statement: Unknown column 'zzz' in 'field list'>

In my SQL table I don't have that column name yet and would expect my append=T will add this column in my SQL table, which apparently it does not.

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
Luke
  • 43
  • 1
  • 4
  • Boh, most likely you have to modify first the table structure, and then you can starting using the new column. – michaJlS Jun 05 '16 at 16:58

2 Answers2

5

What is the name of the unknown column? If it is "row_names" then you need to set the parameter row.names = FALSE in dbWriteTable(). Otherwise, it tries to include the row names in your data frame in the insert statement.

Jai Jeffryes
  • 511
  • 5
  • 12
1

The append will append data to the table, will not do an alter table adding columns.

You need to specify your columns if the name is not the same as in the dataframe using a named list e.g: field.types=list(dte="date", val="double(20,10)")

Daniel Sobrado
  • 717
  • 1
  • 9
  • 22