I need to do retrofit query using update or merge

I have two tables A and B. In A, I have a column called fetch_year. I need to consider table B from these two columns

• primary_date
• secondary_date

These columns have JSON values like {"lock":"true","date":"01/01/1990"} So from this, I need to get the date and I need to extract the year and should save it in table A column called fetch_year. Will always consider primary_date first then secondary_date(if primary_date is null)

The final result should be 1990 in the fetch_year column

Table A is empty as of now( only one column with cal_id)

cal_id   fetch_year 
1            null
n            null

Table B

|B_id|Cal_id |        primary_date               | secondary_date                |
|----|-------|-----------------------------------|------------------------|
|11  |  1    |{"lock":"true","date":"01/01/1990"}|Null|
|12  |  2    |    Null           |  {"lock":"true","date":"01/01/1980"} |
|13  |  3    |    Null           |                  Null |
|14  |  4    | {"lock":"true","date":"01/01/1995"} |{"lock":"true","date":"01/01/1997"} |

In table B So I have n number of records in both the tables I need results like this in A table

Cal_id     fetch_year. 
1             1990
2             1980
3             Null
4             1995
n            n-values

In cal_id =4 in this case we have value in both columns so we are considering primary_date not secondary_date

Please help me with this problem

Answer

You could make use of either JSON_VALUE or OPENJSON here to extract the date from your JSON blobs.

I tend to prefer OPENJSON because it allows you to extract multiple values simultaneously and they don’t have to be at the same level in a nested JSON structure. With the “squirelly” dates in your example data, though, you may prefer the JSON_VALUE version with TRY_CONVERT so that you have more control over date deserialization.

--Data setup
create table dbo.A (
  Cal_id int,
  fetch_year int
);

create table dbo.B (
  B_id int not null identity(11,1),
  Cal_id int,
  primary_date nvarchar(max),
  secondary_date nvarchar(max)
);

insert dbo.A (Cal_id, fetch_year)
values
  (1, null),
  (2, null),
  (3, null),
  (4, null);

insert dbo.B (Cal_id, primary_date, secondary_date)
values
  (1, N'{"lock":"true","date":"01/01/1990"}', null),
  (2, null, N'{"lock":"true","date":"01/01/1980"}'),
  (3, null, null),
  (4, N'{"lock":"true","date":"01/01/1995"}', N'{"lock":"true","date":"01/01/1997"}');

--JSON_VALUE example
update Table_A
set fetch_year = year(coalesce(
  -- REF: CAST and CONVERT / Date and time styles
  -- https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql#date-and-time-styles
  try_convert(date, json_value(primary_date, '$.date'), 101), --mm/dd/yyyy
  try_convert(date, json_value(secondary_date, '$.date'), 101) --mm/dd/yyyy
  ))
from dbo.A Table_A
join dbo.B Table_B on Table_B.Cal_id = Table_A.Cal_id

--OPENJSON example
update Table_A
set fetch_year = year(coalesce(
  Primary_JSON.date,
  Secondary_JSON.date
  ))
from dbo.A Table_A
join dbo.B Table_B on Table_B.Cal_id = Table_A.Cal_id
outer apply openjson(Table_B.primary_date) with ([date] date) Primary_JSON
outer apply openjson(Table_B.secondary_date) with ([date] date) Secondary_JSON;