Sql 修改表的一列中的记录，以另一表中的日期范围为条件

您没有为DB引擎提供标记，所以首先是Postgres，然后是Snowflake，根据您的 comments .

Postgres:个

create table "public"."a" (
  class_id int4, 
  prop_id text
);

create table "public"."b" (
  class_id int, 
  prop_id varchar,
  date_from date,
  date_to date
);

insert into public.a(class_id,prop_id) values
(12,'aa_13'),
(13,'ac_21'),
(22,'ac_11'),
(53,'bb_32'),
(48,'ac_57');

insert into b values
(12,'aa_13','2022-02-15','2022-12-10'),
(12,'aa_31','2021-09-30','2022-02-12'),
(13,'ac_12','2021-05-18','2022-02-05'),
(22,'ac_11','2022-05-12','2022-08-25'),
(22,'ac_12','2022-01-05','2022-04-23'),
(22,'ac_13','2021-08-18','2021-11-16'),
(53,'bb_32','2022-02-06','2022-03-19'),
(53,'bb_31','2021-05-08','2022-02-05'),
(48,'ac_57','2022-02-03','2022-05-07');

select
  a.class_id, 
  a.prop_id,
  case 
      when b.date_from <= make_date(2022,5,1) and b.date_to >= make_date(2022,5,1) + '1 month - 1 day'::interval
      then a.prop_id 
      else null 
  end modified_prop_id
from a left join b on a.prop_id = b.prop_id;

class_id | prop_id | modified_prop_id
-------: | :------ | :---------------
      12 | aa_13   | aa_13           
      22 | ac_11   | null            
      13 | ac_21   | null            
      48 | ac_57   | null            
      53 | bb_32   | null            

db<>fiddle 100

Snowflake:个

create or replace table a (
    class_id number(38,0),
    prop_id varchar(100)
);

create or replace table b (
    class_id number(38,0),
    prop_id varchar(100),
    date_from date,
    date_to date
);

insert into public.a(class_id,prop_id) values
(12,'aa_13'),
(13,'ac_21'),
(22,'ac_11'),
(53,'bb_32'),
(48,'ac_57');

insert into b values
(12,'aa_13','2022-02-15','2022-12-10'),
(12,'aa_31','2021-09-30','2022-02-12'),
(13,'ac_12','2021-05-18','2022-02-05'),
(22,'ac_11','2022-05-12','2022-08-25'),
(22,'ac_12','2022-01-05','2022-04-23'),
(22,'ac_13','2021-08-18','2021-11-16'),
(53,'bb_32','2022-02-06','2022-03-19'),
(53,'bb_31','2021-05-08','2022-02-05'),
(48,'ac_57','2022-02-03','2022-05-07');
      
select
    a.class_id, 
    a.prop_id,
    case 
        when b.date_from <= date_from_parts(2022,5,1) and b.date_to >= last_day(date_from_parts(2022,5,1))
        then a.prop_id 
        else null 
    end modified_prop_id
from a left join b on a.prop_id = b.prop_id;