How to update using subquery [Resolved]

Posted by Karthik2010_Mca under Sql Server on 2/14/2014 | Points: 10 | Views : 1698 | Status : [Member] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Dear All,

I got this error during update my table "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS".

Ex:

UPDATE T_User_Master SET I_Status=1 WHERE I_User_Id in(SELECT tbl.I_User_ID,
tbl.S_Login_ID,
tbl.S_User_Type,
tbl.I_Status,
Login_Time = MAX (tbl.Dt_Login_Time) FROM
(SELECT
TUM.I_User_ID,
TUM.S_Login_ID ,
TLT.Dt_Login_Time,
TUM.S_User_Type,
TUM.I_Status
FROM T_User_Master TUM INNER JOIN T_Login_Trail TLT
ON( TUM.I_User_ID = TLT.I_User_ID )
GROUP BY TUM.I_User_ID,
TUM.S_Login_ID ,
TLT.Dt_Login_Time,
TUM.S_User_Type,
TUM.I_Status
) AS tbl
GROUP BY
tbl.I_User_ID,
tbl.S_Login_ID,
tbl.S_User_Type,
tbl.I_Status
HAVING MAX(tbl.Dt_Login_Time) <= CONVERT(VARCHAR(30), DATEADD(YEAR, -2 ,GETDATE()), 120) and tbl.I_Status=1 --and tbl.S_Login_ID = 'sa'
)

Karthik

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Sravan661 on: 2/14/2014 [Member] Bronze | Points: 50

0	Hi, The inner sub query should Contain the select statement with one Column of same datatype as of column in where clause i.e., tbl.I_User_ID & I_User_Id try this UPDATE T_User_Master SET I_Status=1 WHERE I_User_Id in(SELECT tbl.I_User_ID, FROM (SELECT TUM.I_User_ID, TUM.S_Login_ID , TLT.Dt_Login_Time, TUM.S_User_Type, TUM.I_Status FROM T_User_Master TUM INNER JOIN T_Login_Trail TLT ON( TUM.I_User_ID = TLT.I_User_ID ) GROUP BY TUM.I_User_ID, TUM.S_Login_ID , TLT.Dt_Login_Time, TUM.S_User_Type, TUM.I_Status ) AS tbl GROUP BY tbl.I_User_ID, tbl.S_Login_ID, tbl.S_User_Type, tbl.I_Status HAVING MAX(tbl.Dt_Login_Time) <= CONVERT(VARCHAR(30), DATEADD(YEAR, -2 ,GETDATE()), 120) and tbl.I_Status=1 --and tbl.S_Login_ID = 'sa' ) Hope it helps you revert if you have any issues mark as answer if satisfied . . . sravan Karthik2010_Mca, if this helps please login to Mark As Answer. \| Alert Moderator

Hi,
The inner sub query should Contain the select statement with one Column of same datatype as of column in where clause i.e., tbl.I_User_ID & I_User_Id

try this

UPDATE T_User_Master SET I_Status=1 WHERE I_User_Id in(SELECT tbl.I_User_ID,

FROM

(SELECT

TUM.I_User_ID,

TUM.S_Login_ID ,

TLT.Dt_Login_Time,

TUM.S_User_Type,

TUM.I_Status

FROM T_User_Master TUM INNER JOIN T_Login_Trail TLT

ON( TUM.I_User_ID = TLT.I_User_ID )

GROUP BY TUM.I_User_ID,

TUM.S_Login_ID ,

TLT.Dt_Login_Time,

TUM.S_User_Type,

TUM.I_Status

) AS tbl

GROUP BY

tbl.I_User_ID,

tbl.S_Login_ID,

tbl.S_User_Type,

tbl.I_Status

HAVING MAX(tbl.Dt_Login_Time) <= CONVERT(VARCHAR(30), DATEADD(YEAR, -2 ,GETDATE()), 120) and tbl.I_Status=1 --and tbl.S_Login_ID = 'sa'

)

Hope it helps you
revert if you have any issues
mark as answer if satisfied . . .

sravan

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts