Menu
In my database I have a timestamp column. I need to update a row in the table and need to update the timestamp column. When I run an update command I get:
Cannot update a timestamp column. How can I update the timestamp column?
253
Syntax – Update value to Current Timestamp ALTER TABLE table_name updates table schema. CHANGE column_name updates the column to. column_name TIMESTAMP NOT NULL defines the column as of datatype TIMESTAMP. DEFAULT CURRENT_TIMESTAMP sets the default value of the column to CURRENT_TIMESTAMP.
You unfortunately cannot make a change to a timestamp column, as the error implies; you are stuck with what you have. Also, each table can only have one timestamp column, so you cannot duplicate the column in any solution.
First, specify the table name that you want to change data in the UPDATE clause. Second, assign a new value for the column that you want to update. In case you want to update data in multiple columns, each column = value pair is separated by a comma (,). Third, specify which rows you want to update in the WHERE clause.
You don't
The timestamp column is updated automatically. Perhaps you are under the impression that timestamp contains a value relating to the time? It doesn't, but simply is a number which is updated whenever a value in that record is. Think of it like a row version number.
From MSDN:
The timestamp data type is just an incrementing number and does not preserve a date or a time.
107
You don't update the timestamp column - a timestamp (now renamed rowversion) column is automatically updated by SQL server whenever any other column in the row is updated.
If you already knew this, but for some reason want to force the column to update, just perform an update against the row you want affected. Even if it results in no actual data change, the timestamp column will still update:
create table #T1 ( ID int not null, ts timestamp not null ) insert into #T1 (ID) select 1 union all select 2 select * from #T1 update #T1 set ID = ID where ID=1 select * from #T1 ID ts 1 0x0000000000039AAF 2 0x0000000000039AB0 ID ts 1 0x0000000000039AB1 2 0x0000000000039AB0 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
