Set Field To Have Default Value Of Another

Question

I am trying to create a trigger using the below syntax, however SSMS keeps barking at me with the below error.

Msg 208, Level 16, State 4, Procedure test1, Line 1
Invalid object name 'data123'.

And here is my syntax

CREATE TRIGGER test1
ON data123
AFTER UPDATE
AS
begin
  UPDATE data123
  SET saledate = saledate1
  FROM data123
  INNER JOIN INSERTED i ON data123.id = i.id 
  AND i.saledate1 is not null;
end
go  

Edit
For clarity - what I want to achieve is to set the field saledate = saledate1 where saledate1 is not null.

If there are better ways to achieve this then a trigger I am open for suggestions :)

EDIT #2
I also tried this syntax

ALTER TABLE data123 ADD CONSTRAINT cst123 DEFAULT saledate1 FOR saledate;

But I get an error of

Msg 128, Level 15, State 1, Line 1
The name 'saledate1' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

saledate is a field that is used for multiple SSRS etc and when the database was created etc for data123 the field was improperly named to saledate1. Not viable to try to rename it now as multiple elements are already tied to it. So in order for data123 to be included in SSRS I need the field saledate to hold the value from saledate1

EDIT #3
Here is sample DDL to hopefully clarify what my problem is and my desired output.

Create Table test1
(
  uqID int IDENTITY(1,1) PRIMARY KEY,
  itemsold varchar(400),
  saledate1 date,
  saledate date
)
Insert Into test1 (itemsold, saledate1) Values
('tree', '03/01/2016')
,('flower', '02/11/2016')
,('chair', '02/25/2016')

Select * from test1

So if you run the above syntax this is your output

uqID    itemsold    saledate1   saledate
 1      tree        3/1/2016    
 2      flower      2/11/2016   
 3      chair       2/25/2016   

My desired output (which is why I originally thought a trigger) is this

uqID    itemsold    saledate1   saledate
1       tree        3/1/2016    3/1/2016
2       flower      2/11/2016   2/11/2016
3       chair       2/25/2016   2/25/2016

Answer 1

For a one-off update to make saledate the same as saledate1:

UPDATE dbo.test1
SET saledate = saledate1;

You could also do this with a computed column. This would ensure saledate always matches saledate1 (even when the data changes):

-- Drop the existing column
ALTER TABLE dbo.test1
DROP COLUMN saledate;

-- Add it back as a computed column
ALTER TABLE dbo.test1
ADD saledate AS saledate1;

A third option would be to drop saledate, then rename the saledate1 column:

-- Drop the existing column
ALTER TABLE dbo.test1
DROP COLUMN saledate;

-- Rename saledate1 to saledate
EXECUTE sys.sp_rename 
    @objname = N'dbo.test1.saledate1',
    @newname = N'saledate',
    @objtype = 'COLUMN';

This last approach is more complicated there are dependencies on the column. You would need to remove or update those before performing the rename.

Answer 2

Fully qualify the inserted SaleDate1, i.e. i.saledate1. Remember to add INSERT if you want the trigger to update when the record is inserted.

drop table data123 
create table data123(id int not null identity(1,1),  saledate date, saledate1 date)
go

CREATE TRIGGER test1 ON data123
AFTER update
AS begin
     UPDATE data123
     SET  saledate = i.saledate1
     FROM data123
          INNER JOIN
          INSERTED i
          ON data123.id = i.id
     AND i.saledate1 is not null;
 end
go

insert into data123(saledate1) values('1/1/1901')
select * from data123
update data123 set saledate1 = '3/3/1903'
select * from data123