Why this query produces an error?

Question

Why this query produces an error?

Naomi Nosonovsky 8,771

Hi everybody,

This this query produces an error

;WITH latestTermDate AS (SELECT MAX(status_date) AS dt, tin_number
	                         FROM #FirstResult
							 GROUP BY tin_number
						UNION ALL
    SELECT MAX(hn.load_date) AS dt,
           tin AS tin_number
    FROM rv.hub_tin hn
    WHERE NOT EXISTS
    (
        SELECT 1
        FROM stg.stg_facets_full f
        WHERE f.tin_hk = hn.tin_hk
              AND f.primary_contracted_relationship = 'Clinician'
   
          AND hn.source = 'FACETS')
    GROUP BY hn.tin)
	UPDATE f
	SET [f].[Latest Term Date] = (SELECT MAX(dt), latestTermDate.tin_number FROM latestTermDate 
	                              GROUP BY latestTermDate.tin_number) 
	FROM #FirstResult f 
	JOIN  LatestTermDate cc ON cc.tin_number = f.tin_number
	WHERE Provider_Type IN ('Group', 'Solo') AND Status = 'Termed';

Error is Msg 116, Level 16, State 1, Procedure im_automation.accent_changes, Line 371 [Batch Start Line 2]

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Exactly what the error message says:

SET [f].[Latest Term Date] = (SELECT MAX(dt), latestTermDate.tin_number FROM latestTermDate 
	                              GROUP BY latestTermDate.tin_number)

There are two values in the SELECT list, but you can only assign one.

This would be syntactically correct:

SET [f].[Latest Term Date] = (SELECT MAX(dt) FROM latestTermDate 
	                              GROUP BY latestTermDate.tin_number)

But I doubt that this is what you are looking for, because all rows in #FirstResult will get the same value.

I would guess that this is what you want:

UPDATE f
SET [f].[Latest Term Date] = ltd.Maxdate
FROM #FirstResult f 
JOIN (SELECT MAX(dt) AS Maxdate, latestTermDate.tin_number 
      FROM latestTermDate 
	   GROUP BY latestTermDate.tin_number) AS ltd ON ldt.tin_number = f.tin_number
WHERE Provider_Type IN ('Group', 'Solo') AND Status = 'Termed';

Naomi Nosonovsky 8,771 Reputation points

2025-10-05T22:05:11.9533333+00:00

Thank you, this is exactly what I wanted

Share via

Why this query produces an error?

0 additional answers

Your answer