CODEWITHSUNDEEP
sql-servert-sql
Davide Vitali
Davide Vitali

Reputation: 1035

Error "Subquery returned more than 1 value" when updating a table

I have a stored procedure that fills part of a temporary table with data regarding some documents passed to the procedure using a TVP:

DECLARE @TempDocumentsKeys AS TABLE ( ... );

DocumentID  Keyword         ProtocolNumber   ProtocolDate            FolderIndex
----------- --------------- ---------------- ----------------------- -----------
NULL        ARCHIVIO        277543           2019-10-02 00:00:00.000 111563
NULL        CIAN            277543           2019-10-02 00:00:00.000 111563
NULL        RICHIESTA       277543           2019-10-02 00:00:00.000 111563
NULL        ABILITAZIONI    277543           2019-10-02 00:00:00.000 111563
NULL        ARCHIVIO        277543           2019-10-02 00:00:00.000 128421
NULL        CIAN            277543           2019-10-02 00:00:00.000 128421
NULL        RICHIESTA       277543           2019-10-02 00:00:00.000 128421
NULL        ABILITAZIONI    277543           2019-10-02 00:00:00.000 128421

Now, within the same procedure, I'd have to update this DocumentID field using the newly inserted IDs that I have stored into another temporary table:

DECLARE @InsertedDocuments AS TABLE ( ... );

Keyword      DocumentID  ProtocolNumber   ProtocolDate            FolderIndex
------------ ----------- ---------------- ----------------------- -----------
NULL         81          277543           2019-10-02 00:00:00.000 111563
NULL         82          277543           2019-10-02 00:00:00.000 128421

so that the @TempDocumentsKeys temp table should be looking like this:

DocumentID  Keyword         ProtocolNumber   ProtocolDate            FolderIndex
----------- --------------- ---------------- ----------------------- -----------
81          ARCHIVIO        277543           2019-10-02 00:00:00.000 111563
81          CIAN            277543           2019-10-02 00:00:00.000 111563
81          RICHIESTA       277543           2019-10-02 00:00:00.000 111563
81          ABILITAZIONI    277543           2019-10-02 00:00:00.000 111563
82          ARCHIVIO        277543           2019-10-02 00:00:00.000 128421
82          CIAN            277543           2019-10-02 00:00:00.000 128421
82          RICHIESTA       277543           2019-10-02 00:00:00.000 128421
82          ABILITAZIONI    277543           2019-10-02 00:00:00.000 128421

.I'm trying a simple UPDATE statement

UPDATE @TempDocumentsKeys
SET DocumentID = 
    (SELECT INS.DocumentID FROM @InsertedDocuments AS INS 
    INNER JOIN @TempDocumentsKeys AS TCD ON 
        INS.ProtocolNumber = TCD.ProtocolNumber
        AND INS.ProtocolDate = TCD.ProtocolDate
        AND INS.FolderIndex = TCD.FolderIndex)

but I'm given this error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Why is it? I don't get it, the ON clause within the JOIN should give me one single result, not a set of results...

Thanks, Davide.

Upvotes: 0

Views: 123

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

We can use an updatable CTE here for one option:

WITH cte AS (
    SELECT t1.DocumentID AS DocDest, t2.DocumentID AS DocSrc
    FROM @TempDocumentsKeys t1
    INNER JOIN @InsertedDocuments t2
        ON t1.ProtocolNumber = t2.ProtocolNumber AND
           t1.ProtocolDate = t2.ProtocolDate AND
           t1.FolderIndex = t2.FolderIndex
)

UPDATE cte
SET DocDest = DocSrc;

This approach lets you use syntax which is very similar to your second update attempt, except that this version should actually work on SQL Server.

Upvotes: 2

Zohar Peled
Zohar Peled

Reputation: 82474

Don't use a subquery. SQL Server supports join in update statements:

UPDATE TCD 
SET DocumentID = INS.DocumentID 
FROM @InsertedDocuments AS INS 
INNER JOIN @TempDocumentsKeys AS TCD 
    ON  INS.ProtocolNumber = TCD.ProtocolNumber
    AND INS.ProtocolDate = TCD.ProtocolDate
    AND INS.FolderIndex = TCD.FolderIndex

BTW, What you have is a table variable. That's not exactly the same as a temporary table (which starts with a hashtag or two - #tempTable or ##globalTempTable)

Upvotes: 2

Related Questions