CODEWITHSUNDEEP
sqlsql-server-2008sql-server-2008-r2
user3804276
user3804276

Reputation: 41

SQL Error Explanation

I created a temporary table and I want to update one column but I get an error message.

UPDATE #volume
SET    Pending = (SELECT t1.[esc],
                         t1.[Status],
                         CONVERT(DATE, t1.[Requested Date and time (hh:mm)]),
                         (SELECT count(*)
                          FROM   tmt t2
                          WHERE  t2.[ese] = t1.[eRfx/eAuction]
                                 AND t2.[status] = 'Pending'
                                 AND CONVERT(DATE, t2.[Requested Date and time (hh:mm)]) 
                                     <= CONVERT(DATE, t1.[Requested Date and time (hh:mm)])) cnt
                  FROM   tmt t1)

I am new to SQL Server can anyone explain me the below message

Only one expression can be specified in the select list when the sub query is not introduced with EXISTS.

Raw Data is

## Date      Status ##    
----------
06/16/2014   Pending
06/17/2014   Completed
06/18/2014   Completed
06/19/2014   Pending
06/20/2014   Completed

output for pending status should be like below

----------
06/16/2014     1
06/17/2014     1
06/18/2014     1
06/19/2014     2
06/20/2014     2

Upvotes: 0

Views: 75

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

The error is pretty self-explanatory as noted int he comments. You have multiple items in the select in the subquery in a context where you can have only one item. You are setting the item to a value, so only one is allowed.

It looks like you want to have the count of the pending statuses up to and including each date. You can do this by using a correlated subquery:

UPDATE v
    SET Pending = (select count(*)
                   from tnt
                   where tnt.status = 'Pending' and
                         tnt.date <= v.date
                  )
    FROM #volume v;

Upvotes: 1

Related Questions