SQL Merge (with Temp Table) failing on SubQuery returned more than 1 value (But I am not using a Sub-Query.. in the update)

Question

I am hoping someone can help me out of this tedium...!?

As the title suggests I have a Temp Table (create dynamically in a select statement):

SELECT *
INTO #results
FROM Table_1 
CROSS APPLY (   SELECT TOP 1 *
                FROM Table_2
                WHERE (Table_1.ItemId = Table_2.ItemId)
                ORDER BY CreatedDt DESC
             )

... which as you can see uses a Sub-Query in a cross join.

Next I am trying to use this temp table #results to update a related table with its values. have tried using an update:

UPDATE a
SET a.StatusId = b.StatusId
FROM Table_1    a
INNER JOIN #results     b on (a.ItemId = b.ItemId)

and with a Merge:

MERGE INTO Table_1 a
USING #results b
ON (a.ItemId = b.temId)
WHEN MATCHED THEN UPDATE SET a.StatusId = b.StatusId;

but I seem to always get a response:

Msg 512, Level 16, State 1, Procedure trg_dbo_PBITree_TreeModel_HierarchicalEscalationHistory_InsertNode, Line 7 [Batch Start Line 11] Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

When I query the two tables in question (#results & Table_1) they both have 1 to 1 relationships and cannot see at all where it could be hiding some kind of Subquery!?

Can anyone help quickly on this at all please? This seems to be 1-0-1 stuff and its baking my burger!

-- Edit --

I have taken a look at the Trigger mentioned in the error message as it was suggested it could be trying to handle a single row update instead of a multiple row update which is what I am doing. Nothing looking too unusual to me...?

ALTER TRIGGER [dbo].[trg_dbo_PBITree_TreeModel_HierarchicalEscalationHistory_InsertNode] 
ON [dbo].[Table_1]
AFTER UPDATE 
AS
BEGIN
    -- NodeModelInsertOrUpdateTPH
    IF ((select [Item] from inserted) = 'X')
        BEGIN
            UPDATE tx
            SET 
                tx.LastUpdatedBy = i.LastUpdatedBy,
                tx.LastUpdatedAt = i.LastUpdatedAt
            FROM 
                [dbo].[Table_X] tx,
                inserted i
            WHERE
                tx.OtherItemId = i.OtherItemId
        END
END

Anyone have any ideas?

You have a broken trigger that assumes `inserted` contains exactly one row, rather than the 0, 1 or *multiple* rows, however many were affected by the statement that caused the trigger to fire. — Damien_The_Unbeliever, Dec 01 '17 at 14:05
I think I got you... so the trigger is fine when updating a single value, but as soon as you run it against a join for multiple updates in one transaction it is freaking out? I will need to look into this trigger then - or heaven forbid... iterate!! — CJH, Dec 01 '17 at 14:25
Strange... I'm looking at the Trigger in the error message, and it does seem to be using 'inserted' as a table... I cant see anything suggesting it shouldn't be able to handle this request I don't think!? — CJH, Dec 01 '17 at 14:32
Your IF statement is the problem. If there are even two rows being updated that trigger will fail with that exact message. I would change that around and instead of an if statement make it a where predicate in the update. — Sean Lange, Dec 01 '17 at 14:37

score 1 · Accepted Answer · answered Dec 01 '17 at 14:39

Your trigger is the issue here. Your IF statement has a query which would return more than 1 row and that exact message would be the result. You should make your trigger tolerant of multiple row operations. Here is the same logic but it can handle any number of rows being updated.

ALTER TRIGGER [dbo].[trg_dbo_PBITree_TreeModel_HierarchicalEscalationHistory_InsertNode] 
ON [dbo].[Table_1]
AFTER UPDATE 
AS
BEGIN
    UPDATE tx
    SET 
        tx.LastUpdatedBy = i.LastUpdatedBy,
        tx.LastUpdatedAt = i.LastUpdatedAt
    FROM 
        [dbo].[Table_X] tx
        join inserted i ON tx.OtherItemId = i.OtherItemId
        where i.Item = 'X'
END

Yeah, I think you an Sean above have hit the nail on the head there... Don't know why I didn't see it?!?! Many thanks for that! — CJH, Dec 01 '17 at 15:30

SQL Merge (with Temp Table) failing on SubQuery returned more than 1 value (But I am not using a Sub-Query.. in the update)

1 Answers1