SQL Server子查询报错求助：处理LastName列NULL值时遇Msg 116错误

Why You're Seeing This Error #

The Msg 116 error pops up because your subquery is returning multiple columns or multiple rows in a place where SQL expects only a single value/column. For example, if you tried to use a subquery that selects two columns in a WHERE clause or as a single column in your SELECT list, SQL can't handle that—it needs just one expression there.

Step-by-Step Solution for Your Task #

Your goal has two parts: replace NULL values in the LastName column with "no last name", then output those records. Here's how to do it correctly:

Option 1: Update the Table First, Then Query #

If you want to permanently change the NULL values in your table:

1. Update the NULL values

   UPDATE YourTableName
   SET LastName = 'no last name'
   WHERE LastName IS NULL;
   

   Make sure to replace YourTableName with the actual name of your table.

2. Query the updated records

   SELECT *
   FROM YourTableName
   WHERE LastName = 'no last name';
   

Option 2: Show Replaced Values Without Modifying the Table #

If you don't want to change the original table data and just want to display "no last name" for NULL entries while filtering those records:

SELECT *,
       CASE WHEN LastName IS NULL THEN 'no last name' ELSE LastName END AS DisplayLastName
FROM YourTableName
WHERE LastName IS NULL;

This uses a CASE statement to dynamically replace NULL values in the output, and the WHERE clause filters to only show the rows that originally had NULL in LastName.

Common Mistakes That Cause the Error #

To avoid this in the future, watch out for these missteps:

• Using a subquery that selects multiple columns (e.g., SELECT Col1, Col2 FROM ...) in a spot where SQL expects one value (like after = in a WHERE clause).
• Using a subquery that returns multiple rows with = instead of IN (though that would throw a different error, but similar logic applies).
• Trying to return a subquery as a single column in your SELECT list when it returns more than one row/column.

内容的提问来源于stack exchange，提问作者user7648646