CODEWITHSUNDEEP
sqlsql-servert-sql
Jacque
Jacque

Reputation: 3

Subquery returned more than 1 value error when using a cte

I need to update the QTOTALNETWORTH field if QTOTALNETWORTH < (see the WHERE clause for all the fields). When I use the below query I get the 'Subquery returned more than 1 value...' error. I JOIN the cte to the ContactsQPROFILE by the primary key.

Where am I going wrong in this query? Any help is appreciated. I am using MSSQL.

WITH cte
     AS (SELECT ContactsQPROFILE,
                ( QVALUEOFHOME + QVALUEOFOTHERRE
                  + QRETIREMENTACCT + QLIQUIDASSTINPE + QBUSINESS
                  + QLIFEINSHUSTERM + QLIFEINSHUSPERM
                  + QLIFEINSWIFETER + QLIFEINSWIFEPER ) - QLIABILITIES AS TOTALNETWORTH
         FROM   ContactsQPROFILE
         WHERE  ( ( QVALUEOFHOME + QVALUEOFOTHERRE
                    + QRETIREMENTACCT + QLIQUIDASSTINPE + QBUSINESS
                    + QLIFEINSHUSTERM + QLIFEINSHUSPERM
                    + QLIFEINSWIFETER + QLIFEINSWIFEPER ) - QLIABILITIES > QTOTALNETWORTH )
                AND ( ( QVALUEOFHOME + QVALUEOFOTHERRE
                        + QRETIREMENTACCT + QLIQUIDASSTINPE + QBUSINESS
                        + QLIFEINSHUSTERM + QLIFEINSHUSPERM
                        + QLIFEINSWIFETER + QLIFEINSWIFEPER ) > 0.00 ))
UPDATE cqp
SET    cqp.QTOTALNETWORTH = cte.TOTALNETWORTH
FROM   ContactsQPROFILE cqp
       JOIN cte
         ON cte.ContactsQPROFILE = cqp.ContactsQPROFILE

Upvotes: 0

Views: 255

Answers (3)

Jacque
Jacque

Reputation: 3

I was getting the error due to the trigger. As soon as I disabled it I was able to run the UPDATE query without using CTE. I then enabled the trigger.

Upvotes: 0

sacse
sacse

Reputation: 3744

I think you can directly update the table:

UPDATE cqp
SET cqp.QTOTALNETWORTH = (QVALUEOFHOME + QVALUEOFOTHERRE + QRETIREMENTACCT + QLIQUIDASSTINPE + QBUSINESS + QLIFEINSHUSTERM + QLIFEINSHUSPERM + QLIFEINSWIFETER + QLIFEINSWIFEPER) - QLIABILITIES
FROM ContactsQPROFILE cqp
WHERE ((QVALUEOFHOME + QVALUEOFOTHERRE + QRETIREMENTACCT + QLIQUIDASSTINPE + QBUSINESS + QLIFEINSHUSTERM + QLIFEINSHUSPERM + QLIFEINSWIFETER + QLIFEINSWIFEPER) - QLIABILITIES 
    > QTOTALNETWORTH)
    AND   ((QVALUEOFHOME + QVALUEOFOTHERRE + QRETIREMENTACCT + QLIQUIDASSTINPE + QBUSINESS + QLIFEINSHUSTERM + QLIFEINSHUSPERM + QLIFEINSWIFETER + QLIFEINSWIFEPER) > 0.00)

Upvotes: 1

NicVerAZ
NicVerAZ

Reputation: 407

You do not need a CTE in this case because you are updating ContactsQPROFILE based on conditions internal to ContactsQPROFILE using columns only from ContactsQPROFILE.

That said, run the declarative part of the CTE alone and see if it generates duplicates for some reason.

Upvotes: 0

Related Questions