Sql error number: 512. Error Message: Subquery returned more than 1 value

I am trying to run this query in SSMS and I’m getting an error, ‘Sql error number: 512. Error Message: Subquery returned more than 1 value’

MERGE datawarehouse.product t
        USING
        (
            SELECT
            (StyleCode + ColorwaySAPID) as OptionID,
            ColorwayName,
            StyleCode, 
            [Description],
            Division,
            lclChannel,
        
            (select [label] FROM import.enumneckline e 
            inner join import.Product p 
                ON e.Enumeration_Value = p.lclNeckLine2

                ) as NeckLine,

                ValidationFlag,
                ValidationMessage,
                [Timestamp],
                [Customer_Timestamp]

                FROM import.product 

            )   s
         
    ON t.OptionID = s.OptionID

    WHEN MATCHED THEN
        UPDATE
            SET
                
t.OptionIDName = s.ColorwayName,
t.StyleID = s.StyleCode,
t.StyleName = s.[Description],
t.DepartmentID = s.Division,
t.RegionChannelName  = s.lclChannel,
t.lclNeckline2 = s.[Neckline],
t.ValidationFlag = 'OK',
T.ValidationMessage = NULL,
T.[Timestamp] = S.[Timestamp],
T.[Customer_Timestamp] = S.[Customer_Timestamp]
    
    WHEN NOT MATCHED BY TARGET THEN
        INSERT
            (
                OptionId,
OptionIDName,
StyleID ,
StyleName,
DepartmentID,
RegionChannelName,
lclNeckline2,
ValidationFlag,
ValidationMessage,
[TimeStamp],
Customer_Timestamp
            )
        VALUES
            (
                s.OptionID,
s.ColorwayName,
s.StyleCode,
s.[Description],
s.Division,
s.lclChannel,
s.[Neckline],
'Ok',
s.ValidationMessage,
s.[TimeStamp],
s.Customer_Timestamp

            );

I am not sure what I’m doing wrong here. The join is returning only 1 value = ‘label’ and I am not using the table object for pulling any other data. What am I doing incorrectly here?

any help will be appreciated

I’m expecting the DWH table to be loaded with the select values

>Solution :

Likely the problem is with the subquery that returns NeckLine:

SELECT (StyleCode + ColorwaySAPID) as OptionID,
    ...,
    (
        SELECT [label] 
        FROM import.enumneckline e 
        INNER JOIN import.Product p 
            ON e.Enumeration_Value = p.lclNeckLine2
    ) as NeckLine,
    ...
FROM import.product 

There is no WHERE clause in the subquery that limits which rows are returned, so this brings all rows that match between the two tables.

You are already selecting from product in the outer query, so I really suspect that you meant a correlated subquery instead:

SELECT (StyleCode + ColorwaySAPID) as OptionID,
    ...,
    (
        SELECT [label] 
        FROM import.enumneckline e 
        WHERE e.Enumeration_Value = p.lclNeckLine2  -- correlation here
    ) as NeckLine,
    ...
FROM import.product p                               -- alias for the outer table