i'm working on a project where i have to alter a tables defenition but i need to keep the data there's already there and i don't know how to accomplish this.
the actual table struct is as follows
[IDDocumento (int)]
[IDTipoDocumento (int)]
[IDEntidade (int)]
[Data (datetime)]
[Designacao (varchar 100)]
[TotalIliquido (money)]
[TotalImposto (money)]
[TotalRetencoes (money)]
[TotalLiquido (money)]
[Anulado (bit)]
what i need to do here is to drop the 'Anulado' column and add 3 new columns, maintaining the stored data, and giving a value to one of the new columns if 'Anulado' was TRUE
what i have is
ALTER TABLE dbo.Documentos
ADD
IDEstadoDocumento int null,
DataVencimento datetime null,
ValorAberto decimal(18,5) not null CONSTRAINT DF_Documentos_ValorAberto DEFAULT 0;
//HERE IS WHERE I NEED TO CHECK IF 'ANULADO' IS TRUE OR NOT AND CHANGE 'IDEstadoDOCUMENTO' TO 3 WHEN IT IS
ALTER TABLE dbo.Documentos
DROP COLUMN Anulado;
How am i suposed to do this?
I simple update should suffice
UPDATE dbo.Documentos SET IDEstadoDocumento = 3 WHERE Anulado = 1