Menu
I have a data frame in R having 3 columns, using sqlSave I can easily create a table in an SQL database:
channel <- odbcConnect("JWPMICOMP")
sqlSave(channel, dbdata, tablename = "ManagerNav", rownames = FALSE, append = TRUE, varTypes = c(DateNav = "datetime"))
odbcClose(channel)
This data frame contains information about Managers (Name, Nav and Date) which are updatede every day with new values for the current date and maybe old values could be updated too in case of errors.
How can I accomplish this task in R?
I treid to use sqlUpdate but it returns me the following error:
> sqlUpdate(channel, dbdata, tablename = "ManagerNav")
Error in sqlUpdate(channel, dbdata, tablename = "ManagerNav") :
cannot update ‘ManagerNav’ without unique column
729
To modify table data through a view. In Object Explorer, expand the database that contains the view and then expand Views. Right-click the view and select Edit Top 200 Rows. You may need to modify the SELECT statement in the SQL pane to return the rows to be modified.
SQL UPDATE Syntax To use the UPDATE method, you first determine which table you need to update with UPDATE table_name . After that, you write what kind of change you want to make to the record with the SET statement. Finally, you use a WHERE clause to select which records to change.
The Syntax for SQL UPDATE Command The UPDATE statement lets the database system know that you wish to update the records for the table specified in the table_name parameter. The columns that you want to modify are listed after the SET statement and are equated to their new updated values. Commas separate these columns.
When you create a table "the white shark-way" (see documentation), it does not get a primary index, but is just plain columns, and often of the wrong type. Usually, I use your approach to get the columns names right, but after that you should go into your database and assign a primary index, correct column widths and types.
After that, sqlUpdate() might work; I say might, because I have given up using sqlUpdate(), there are too many caveats, and use sqlQuery(..., paste("Update....))) for the real work.
107
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
