sql update data from r dataframe not insert

I have a dataframe (df) with three columns (a,b,c)

I am inserting these values into SQL database using

df <- data.frame(a=1:10, b=10:1, c=11:20)
values <- paste("(",df$a,",", df$b,",",df$c,")", sep="", collapse=",")
cmd <- paste("insert into MyTable values ", values)
result <- sqlQuery(con, cmd, as.is=TRUE)

Source: How to insert a dataframe into a SQL Server table?

My question is what is the update equivalent of this example ? How can i use

cmd <- paste("update MyTable values ", values)
result <- sqlQuery(con, cmd, as.is=TRUE)

I tried sqlSave and sqlUpdate but it got sticky too soon with error messages like missing columns, index etc...so I am falling back to this example above and trying to learn how to use update statements based on that toy dataset. Any help is much appreciated folks.

Solution

So, firstly you have the wrong syntax for UPDATE. In general,

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

so you can't build up the values as a concatenated vector as you have done. If you don't select a particular element with the WHERE you will update the value value1 across all the values in column1.

EDIT: If you can't match the condition, then you aren't actually updating, you are inserting, which has the forms:

It is possible to write the INSERT INTO statement in two forms.

The first form does not specify the column names where the data will be inserted, only their values:

INSERT INTO table_name
VALUES (value1,value2,value3,...);

The second form specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

If you want to do anything more complicated, you will need to build up the query with SQL, probably in something other than R first, at least to learn. A possibility for experimentation could be SQL fiddle if you aren't comfortable with SQL yet.