question

craftybooth avatar image
craftybooth asked

How do I use the population date of one row/column to populate the date in another row/column?

I need to populate a column with the date that another column in the same table was first populated and then have that date remain static even if the source column is updated. This date can be different for every row in the table.

datecolumnautomationrow
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered

Use a trigger to to identify when the first column is populated, and then update the second column where it is not null.

Some example code:

use tempdb;
if object_id('YourTable') is not null drop table YourTable;
create table YourTable 
  (id int identity(1,1), col1 datetime, someothercol varchar(10), col2 datetime);
go
create trigger YourTableTrigger on dbo.YourTable
for insert, update
as
if update(col1)
begin
	update YourTable
	set col2=getdate()
	where col1 is not null and col2 is null
	and id in (select id from inserted);


end;
go


-- insert some data without populating the col1 column
insert into dbo.YourTable (col1, someothercol)
select top 10 null,'blah';
go 10
-- see that col2 remains unpopulated
select * from dbo.YourTable;

/*
id          col1                    someothercol col2
----------- ----------------------- ------------ -----------------------
1           NULL                    blah         NULL
2           NULL                    blah         NULL
3           NULL                    blah         NULL
4           NULL                    blah         NULL
5           NULL                    blah         NULL
6           NULL                    blah         NULL
7           NULL                    blah         NULL
8           NULL                    blah         NULL
9           NULL                    blah         NULL
10          NULL                    blah         NULL

(10 rows affected)
*/


-- update col1 in some rows
update dbo.YourTable
set col1='1 Jan 2018'
where id%2=1;
-- and see that col2 has been populated
select * from dbo.YourTable;
/*
id          col1                    someothercol col2
----------- ----------------------- ------------ -----------------------
1           2018-01-01 00:00:00.000 blah         2018-09-28 08:31:06.413
2           NULL                    blah         NULL
3           2018-01-01 00:00:00.000 blah         2018-09-28 08:31:06.413
4           NULL                    blah         NULL
5           2018-01-01 00:00:00.000 blah         2018-09-28 08:31:06.413
6           NULL                    blah         NULL
7           2018-01-01 00:00:00.000 blah         2018-09-28 08:31:06.413
8           NULL                    blah         NULL
9           2018-01-01 00:00:00.000 blah         2018-09-28 08:31:06.413
10          NULL                    blah         NULL

(10 rows affected)
*/

-- update them again
update dbo.YourTable
set col1='2 Jan 2018'
where id%2=1;
-- but see that col2 has remained static
select * from dbo.YourTable;
/*
id          col1                    someothercol col2
----------- ----------------------- ------------ -----------------------
1           2018-01-02 00:00:00.000 blah         2018-09-28 08:31:06.413
2           NULL                    blah         NULL
3           2018-01-02 00:00:00.000 blah         2018-09-28 08:31:06.413
4           NULL                    blah         NULL
5           2018-01-02 00:00:00.000 blah         2018-09-28 08:31:06.413
6           NULL                    blah         NULL
7           2018-01-02 00:00:00.000 blah         2018-09-28 08:31:06.413
8           NULL                    blah         NULL
9           2018-01-02 00:00:00.000 blah         2018-09-28 08:31:06.413
10          NULL                    blah         NULL

(10 rows affected)
*/

-- now insert a single row with col1 poplated
insert into dbo.YourTable (col1, someothercol)
select '1 feb 2018','new blah';
-- and see that col2 gets populated too
select * from dbo.YourTable;
/*
id          col1                    someothercol col2
----------- ----------------------- ------------ -----------------------
1           2018-01-02 00:00:00.000 blah         2018-09-28 08:31:06.413
2           NULL                    blah         NULL
3           2018-01-02 00:00:00.000 blah         2018-09-28 08:31:06.413
4           NULL                    blah         NULL
5           2018-01-02 00:00:00.000 blah         2018-09-28 08:31:06.413
6           NULL                    blah         NULL
7           2018-01-02 00:00:00.000 blah         2018-09-28 08:31:06.413
8           NULL                    blah         NULL
9           2018-01-02 00:00:00.000 blah         2018-09-28 08:31:06.413
10          NULL                    blah         NULL
11          2018-02-01 00:00:00.000 new blah     2018-09-28 08:31:06.417

(11 rows affected)
*/

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks! That's exactly what I was looking for. I was waaaay overthinking this.

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.