准备数据的脚本：

CREATE TABLE t1 (
  [date] datetime,
  col1 varchar(50),
  col3 varchar(50),
  col4 numeric(18,6)
);

CREATE TABLE t2 (
  [date] datetime,
  col1 varchar(50)
);

INSERT INTO t1 ([date], col1, col3, col4) VALUES ('2020-05-12', 'xyz', 'ttx',  1)
INSERT INTO t1 ([date], col1, col3, col4) VALUES ('2020-05-16', 'xyz', 'abc',  5)
INSERT INTO t1 ([date], col1, col3, col4) VALUES ('2019-05-15', 'xyz', 'abc',  2)
INSERT INTO t1 ([date], col1, col3, col4) VALUES ('2019-05-11', 'xyz', 'ttx',  3)
INSERT INTO t1 ([date], col1, col3, col4) VALUES ('2020-05-19', 'xyx', 'abc',  4)

INSERT INTO t2 ([date], col1) VALUES ('2020-05-14', 'ttx')
INSERT INTO t2 ([date], col1) VALUES ('2020-05-18', 'abc')
INSERT INTO t2 ([date], col1) VALUES ('2020-05-19', 'abc')

首先，让我们选择所需的输出：

SELECT 
  (SELECT TOP 1 t1.[date]
     FROM t1
    WHERE t1.col3 = t2.col1
      AND t2.[date] >= t1.[date]
    ORDER BY 1 DESC) [date],
  t2.col1
FROM t2

说明：我们从t2中选择行，从t1中的匹配记录中选择日期，我们通过 [date] 条件( t2.[date] >= t1.[date] )，所以我们可以按照您的要求获得匹配日期或之前的可用日期。
同样的方法也可以使用外部应用

SELECT t1_.[date], t2.col1
  FROM t2
  OUTER APPLY (SELECT TOP 1 t1.[date]
                 FROM t1 WHERE t1.col3 = t2.col1
                           AND t2.[date] >= t1.[date]
                ORDER BY 1 DESC) t1_

注意：您必须处理空值，不清楚该怎么办。选项可以是：忽略，或使用它们删除t2中的记录-这取决于您如何使用它。
现在，对于更新-查询的外部应用形式在这里更有用-基于它，您可以编写：

UPDATE t2
   SET [date] = t1_.[date]
  FROM t2
  OUTER APPLY (SELECT TOP 1 t1.[date]
                 FROM t1 WHERE t1.col3 = t2.col1
                          AND t2.[date] >= t1.[date]
                ORDER BY 1 DESC) t1_

同样，要小心空值。您可以将outer apply替换为cross apply，这样会忽略空值（如果没有匹配的记录，则不更新t2中的行，即使是之前的日期）。
如果要检查空值并引发错误，请将以下内容放在脚本开头：

IF EXISTS (
     SELECT 1
       FROM t2
      WHERE NOT EXISTS (
                  SELECT 1
                    FROM t1
                   WHERE t2.[date] >= t1.[date]
                     AND t1.col3 = t2.col1
                )
           )
  RAISERROR (N'No value in t2, which match date or prior date in t1', 16, 1)

您可以使用

SELECT *
   FROM t2
  WHERE NOT EXISTS (
             SELECT 1
               FROM t1
              WHERE t2.[date] >= t1.[date]
                AND t1.col3 = t2.col1
           )
      )

我正在尝试将t2记录与t1表匹配。如果匹配，则匹配的记录应返回。如果它不匹配，那么我们需要检查t1中先前的可用日期，并用匹配的日期更新t2。
这就是你想要的吗？

select t2.*, t1.date as new_date
from t2 outer apply
     (select top (1) t1.*
      from t1
      where t1.col3 = t2.col3 and t1.date <= t2.date
      order by t1.date desc
     ) t1;

如果你想要一个 update ，然后：

with toupdate as (
      select t2.*, t1.date as new_date
      from t2 outer apply
           (select top (1) t1.*
            from t1
            where t1.col3 = t2.col3 and t1.date <= t2.date
            order by t1.date desc
           ) t1
     )
update toupdate
     set date = new_date
     where new_date <> date;