I think you're confusing the Columns option and the WHERE Clause. If you have one or two fields that uniquely identify a row or rows, then set your Row Selection to Columns and select your fields. However, with a WHERE Clause, you need to specify an actual SQL clause that would identify the row or rows that you want to update. 

Think of it as if you were selecting one or more features from within ArcGIS. Your WHERE Clause is what you'd type into the selection or definition query window. Something like 

OBJECTID = '3'

or 

MATERIAL = 'Wood'

The first one would uniquely identify a single row, and the second could identify one or more rows to update.

If you want to use attributes from upstream, then you could define your where clause like this:

OBJECTID = @value(OBJECTID)

Or

MATERIAL = @value(MATERIAL)

Hope this helps.

Thanks rchoucroun. I used the first option you show there that would uniquely identify one row. I should have been more clear. I first select the unique record that I want to update using a tester and then change the values I need using user paramaters and an attribute manager. Finally, I have the Microsoft SQL Server Non-Spatial Writer where I again have the where clause set to those same values as the tester. But while it is bringing through and showing the row and the new attributes that the user has set using the user paramaters, it isn't actually updating the table. I do the same thing on the other tables that have to be updated that don't have the identity field and I am using just the normal ESRI Geodatabase (ArcSDE Geodb) and the update works. But with the different writer it won't update. I'm assuming it has to have a different setting changed. I just can't figure out which one.

@chris28​ It doesn't look like identity columns used to be supported on SQL Server by Esri. Is it still the case in more recent versions?

Also, if your Geodb feature classes are versioned then updating the native tables might not work

I believe it is supported now as I can add a new record. It just won't let me update a record right now. Currently, I'm in the testing phase and the feature classes aren't versioned. Once we go into production they will change to versioned.

@chris28​ There is some risk involved in writing to the native database if the ArcSDE is versioned. You need to know which view you have to write too. If you've done this before then that's OK, but we'd usually recommend using the Geodatabase writer when working with versioned databases.

Here it mentioned that OBJECTID uses an identity property - but it doesn't mention that on other field data types. You might need to contact Esri.