I have a table with a column LastUpdated of type DateTime, and would like to add a column LastUpdated2 to this table. I would like to populate it with whatever LastUpdated is for each existing row in the table.

Eg:

Becomes:

Quite simply as you can see, I just want them to match.

I see a lot of example out there for an ALTER statement that has a default value, but didn't find any that have a specific value for each row as they're updated.

Optimally I'd want my code to be something like this, hopefully this pseudocode makes sense:

ALTER TABLE dbo.Appointments
    ADD LastUpdated2 DATETIME
        DEFAULT (SELECT LastUpdated FROM CurrentRow)

I've also considered maybe just doing an ALTER statement, and then an UPDATE statement. Maybe this is the only way how to do it?

CodePudding user response：

I'm thinking there is no way to do this all within the ALTER statement by itself. Needs to be two separate statements:

ALTER TABLE dbo.Appointments
ADD LastUpdated2 DATETIME

UPDATE dbo.Appointments
SET LastUpdated2 = LastUpdated

This accomplishes what I am trying to do I think. I was curious if there was a way to do it inside of just the ALTER statement, but maybe not. I don't see any examples online.

CodePudding user response：

You could add it as a computed column, persisted if you want it to actually store the data to disk:

ALTER TABLE dbo.Appointments
ADD LastUpdated2 AS LastUpdated PERSISTED;

The caveat to doing this, is it will always be based off of the LastUpdated column, as it changes. If you need it to only match the LastUpdated column initially, then doing a two step process via ALTER then UPDATE like your answer is the way.