'Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. The updated data have to be calculated first

Here is my query:

UPDATE [price].[dbo].[DataClean]
SET MinPrice = (SELECT 
                    [ID], [Item_Name],
                    MIN([Price]) AS MinPrice
                FROM 
                    [price].[dbo].[MasterItem] with (nolock) 
                WHERE
                    Price IS NOT NULL
                    AND Sold IS NOT NULL
                GROUP BY
                    [ID], [Item_Name])

Any idea will be appreciated. Thank you

sqlsql-serversubquery


Solution 1:[1]

You have 3 columns listed in your select statement in the sub query.

But, you are trying to update one column in the target table Dataclean.

Try something like

;WITH CTE_MasterItem AS (
SELECT 
    [ID]
    ,[Item_Name]
    ,MIN([Price]) AS [MinPrice]
FROM [price].[dbo].[MasterItem] WITH(NOLOCK) 
WHERE [Price] IS NOT NULL
    AND [Sold] IS NOT NULL 
GROUP BY 
    [ID]
    ,[Item_Name]
)

UPDATE [price].[dbo].[DataClean]
SET MinPrice = cte.MinPrice 
FROM CTE_MasterItem AS cte

then put a WHERE clause in the UPDATE statement if needed

*I haven't tested this as I don't have SSMS

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions