How to update using subquery [Resolved]

Posted by Karthik2010_Mca under Sql Server on 2/14/2014 | Points: 10 | Views : 1045 | Status : [Member] | Replies : 1
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


Responses

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

Up
0
Down

Resolved
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

Login to post response