Adding custom value to SELECT

I have this query:

SELECT r.record_id,
       r.part_id,
       r.record_dt,
       a.association_part_a,
       a.association_part_b,
       a.association_type,
       a.association_id,
       r.product_id
FROM   dbo.synfact_association AS a
       INNER JOIN dbo.synfact_record AS r
               ON a.record_id = r.record_id
WHERE  ( r.part_id IN (SELECT part_id
                       FROM   dbo.synfact_record AS synfact_record_1
                       WHERE  ( record_status = 1 )
                              AND ( record_type = 0 )) )
       AND ( r.product_id IN ( 38, 39, 40, 41,
                               42, 43, 44, 45,
                               46, 37, 47, 48,
                               49, 50, 51, 52,
                               53, 54, 58, 59 ) )
       AND ( r.record_id > 499 )
       AND ( r.record_status = 1 ) 

I want to use the record_dt of the second query as a replacement of the record_dt in the first query. I want to do that by linking the values on the part_id since they both have it. First query is giving a list of record_id that has part_id, the second query is taking the latest record_dt for each record_id. My end goal is to take the latest record_dt from the second query to replace in the first query. I can link them both on the part_id.

I need to replace the r.record_dt with this SELECT:

SELECT DISTINCT RECORD_DT FROM synfact_record WHERE PROCESS_STEP_LIST_ID IN (21,22,23) ORDER BY RECORD_DT DESC

This is what the first query does

RECORD_ID	PART_ID	RECORD_DT	ASSOCIATION_PART_A	ASSOCIATION_PART_B	ASSOCIATION_TYPE	ASSOCIATION_ID	PRODUCT_ID
286660	SYN12021020100018	2021-02-15 11:18:11.840	SYN12021020100018	21-02-01-000003	unique_id	452028	39
287146	SYN12021020300773	2021-02-17 07:30:59.603	SYN12021020300773	2102-00-005218	unique_id	455735	38
287147	SYN12021020300774	2021-02-17 07:31:04.780	SYN12021020300774	2102-00-005219	unique_id	455736	38

The second query returns:

RECORD_DT
2021-10-20 11:36:02.670
2021-10-20 11:35:29.263
2021-10-20 11:34:59.583

I want to replace the record_dt by the one with the second SELECT.

I know I must append it to the first query, I just don’t know how…

Which method is best to do this?

Answer

If You can LIMIT 1 to your select clause, you can use query like this:

SELECT r.record_id,
       r.part_id,
    CASE
        WHEN r.PROCESS_STEP_LIST_ID IN (21 , 22, 23) THEN 
            (SELECT DISTINCT RECORD_DT FROM synfact_record WHERE PROCESS_STEP_LIST_ID IN (21,22,23) ORDER BY RECORD_DT DESC LIMIT 1)
        ELSE r.RECORD_DT
    END as record_dt,
       a.association_part_a,
       a.association_part_b,
       a.association_type,
       a.association_id,
       r.product_id
FROM   dbo.synfact_association AS a
       INNER JOIN dbo.synfact_record AS r
               ON a.record_id = r.record_id