How can I get a single value returned from a subquery in an update statement? [closed]

Question

I need to update a table with a single value returned per record from my subquery.

I have a History table with the following values:

Company|EmplNo|Status| Code |Level|Level_Date|Entry_Date
5900   |324778| B    |31117 | 1   | 1/30/1995| 1/29/1995
5900   |324778| B    |31117 | 1   | 3/25/1996| 1/29/1995
5900   |324778| B    |31117 | 2   | 3/25/1996|10/15/1996
5900   |324778| B    |31118 | 1   |12/13/1999|12/12/1999
5900   |324778| B    |31118 | 1   |12/13/1999| 9/16/2003
5900   |324778| L    |31118 | 1   |12/13/1999| 9/16/2003
5900   |324778| Q    |31117 | 2   | 3/25/1996|10/15/1996
5900   |324778| Q    |31118 | 1   |12/13/1999|12/12/1999
7900   |344778| B    |1005C | 0   | 12/6/2021| 12/5/2021

I have a reference table with the following values:

Company|EmplNo|Status| Code |Level|Entry_Date|SeqNo|FName|LName
5900   |324778| L    |31118 | 1   |12/13/1999| 1   |Jon  | Due
7900   |344778| B    |1005C | 0   | 12/6/2021| 1   |Jon  | Due

Using the data in the Reference and History tables, I am trying to update the following transaction table with the minimum Level_Date, which should be 12/13/1999 for Code 31118, and 12/6/2021 for Code 1005C.

Company|EmplNo|Status| Code |Level|Level_Date|Entry_Date
5900   |324778| L    |31118 | 1   |          | 12/13/1999
7900   |344778| B    |1005C | 0   |          | 12/6/2021

My SQL Server script is as follows:

So sorry about the image. Did not realize the codes were used, and for some reason I could not find the formatting characters to include my code. The code is:

UPDATE [Emp_Dates] 
   set [Level_Date]= 
       (  SELECT MIN(CONVERT(VARCHAR(10), CAST(H.Level_Date AS DATETIME), 112)) as Level_Date 
            FROM [Emp_History] H INNER JOIN 
          Emp_Dates D ON 
            D.Company = H.Company AND 
            D.EmplNo = H.EmplNo AND
            D.Code = H.Code AND
CONVERT(VARCHAR(10), CAST(D.Level_Date AS DATETIME), 112) = CONVERT(VARCHAR(10), CAST(H.Level_Date AS DATETIME), 112) AND
   D.EMPL_NUM = '0324778'
             GROUP BY H.Company, H.EmplNo, H.Code, H.Level_Date  )

The problem is that I keep getting the following error message:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

However, when I take the subquery out of the Update statement and run it by itself, I get one output for each record, which should allow the query to populate the Level_Date for each record.

The result should be as follows:

Company|EmplNo|Status| Code |Level|Level_Date|Entry_Date
5900   |324778| L    |31118 | 1   | 19991212 | 12/13/1999
7900   |344778| B    |1005C | 0   | 20211205 | 12/6/2021

Can you help me figure out what is happening? Thanks.

Answer 1

As written, there is no correlation between the Emp_Dates records being updated and the Emp_Dates records being referenced inside the subquery. Even if they were correlated, the H.Level_Date part of the GROUP BY would likely lead to multiple groups, each having its own calculated MIN(Level_Date), yielding multiple values and an error.

You need to include (and alias) Emp_Dates in a FROM clause of the outer UPDATE statement and eliminate the second reference within the subquery. The subquery may then reference the Emp_Dates in the outer statement. The GROUP BY would then be eliminated. The D.EMPL_NUM = '0324778' condition should also move to the outer statement.

Something like the following should be closer to what you are looking for:

UPDATE D 
set Level_Date = (
    SELECT MIN(CONVERT(VARCHAR(10), CAST(H.Level_Date AS DATETIME), 112)) as Level_Date
    FROM [Emp_History] H
    WHERE H.Company = D.Company
    AND H.EmplNo = D.EmplNo
    AND H.Code = D.Code
    AND CONVERT(VARCHAR(10), CAST(H.Level_Date AS DATETIME), 112)
      = CONVERT(VARCHAR(10), CAST(D.Level_Date AS DATETIME), 112)
)
FROM Emp_Dates D
WHERE D.EMPL_NUM = '0324778'

There may be separate issues with the way Level_Date is stored is you need to convert from text to datetime and then truncate to date. Ideally, you should be storing dates and date/times using native SQL Server DATE, DATETIME, or DATETIME2 types. Truncating to date would then be more simply CONVERT(DATE, X.Level_Date).

ADDENDUM: Per discussion in the comments below, here is an example using CROSS APPLY that lets you calculate multiple values in a single subquery.

UPDATE D 
set Level_Date = A.Min_Level_Date, Level_Date2 = A.Max_Level_Date
FROM Emp_Dates D
CROSS APPLY (
    SELECT
        MIN(CONVERT(VARCHAR(10), CAST(H.Level_Date AS DATETIME), 112)) as Min_Level_Date,
        MAX(CONVERT(VARCHAR(10), CAST(H.Level_Date AS DATETIME), 112)) as Max_Level_Date
    FROM [Emp_History] H
    WHERE H.Company = D.Company
    AND H.EmplNo = D.EmplNo
    AND H.Code = D.Code
    AND CONVERT(VARCHAR(10), CAST(H.Level_Date AS DATETIME), 112)
      = CONVERT(VARCHAR(10), CAST(D.Level_Date AS DATETIME), 112)
) A
WHERE D.EMPL_NUM = '0324778'

You can even use additional CROSS APPLY clauses to calculate repeated expressions to eliminate duplicate code.

UPDATE D 
set Level_Date = A.Min_Level_Date, Level_Date2 = A.Max_Level_Date
FROM Emp_Dates D
CROSS APPLY (
    SELECT CONVERT(VARCHAR(10), CAST(D.Level_Date AS DATETIME), 112) AS D_date
) DD
CROSS APPLY (
    SELECT
        MIN(HD.H_Date) as Min_Level_Date,
        MAX(HD.H_Date) as Max_Level_Date
    FROM [Emp_History] H
    CROSS APPLY (
        SELECT CONVERT(VARCHAR(10), CAST(H.Level_Date AS DATETIME), 112) AS H_date
    ) HD
    WHERE H.Company = D.Company
    AND H.EmplNo = D.EmplNo
    AND H.Code = D.Code
    AND HD.H_Date = DD.D_Date
) A
WHERE D.EMPL_NUM = '0324778'