SQL Server 2008(SP4)字段更新报错：子查询返回多值问题求助

Let's break down your issues one by one and fix them:

1. Why UPDATE IGNORE throws a syntax error #

UPDATE IGNORE is a MySQL-specific syntax — SQL Server doesn't support it. You can safely drop the IGNORE keyword; SQL Server's standard UPDATE statement is what you need here.

2. Fix the "Subquery returned more than 1 value" error #

This error isn't coming from your UPDATE statement directly — it's caused by the trigger on the [DBO].[Details] table. Here's why:
When you update multiple rows at once, SQL Server's DELETED system table contains all the old values of the rows being modified. Your original trigger tries to assign multiple IGXF values from DELETED to a single variable @IGI, which is impossible (a variable can only hold one value at a time).

Updated Trigger Code #

Replace your existing trigger with this version, which uses set-based logic to handle multiple rows correctly:

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Delete all matching records in SUPPORT using the DELETED table directly
    DELETE FROM DBO.SUPPORT 
    WHERE IGBA IN (SELECT IGXF FROM DELETED)
END

This works because DELETED contains every row being updated, and the IN clause matches all corresponding IGBA values in the SUPPORT table — no single variable needed.

3. Run your UPDATE statement correctly #

With the fixed trigger, your original update query will now execute without errors:

UPDATE [DBO].[Details] 
SET RisInt = REPLACE(RisInt, ';', '-'), 
    RisNot = REPLACE(RisNot, ';', '-')

Optional: Optimize the Update #

If you only want to update rows that actually contain ; (to avoid unnecessary writes), add a WHERE clause:

UPDATE [DBO].[Details] 
SET RisInt = REPLACE(RisInt, ';', '-'), 
    RisNot = REPLACE(RisNot, ';', '-')
WHERE RisInt LIKE '%;%' OR RisNot LIKE '%;%'

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