Geting Error in Delete Query

Question

DELETE FROM Loan_GL_LoanDetails Where loan_id IN(SELECT TOP 1 * FROM Loan_GL_LoanDetails LD Inner Join Loan_GL_LoanMaster LM on LM.loan_id=LD.loan_id where LM.loan_no = @loan_no ORDER BY LD.ld_id DESC) Error : Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Ajith kp · Answer

Here My problem is.. want to delete last row from table-2 based on the table-1 value. DELETE FROM Loan_GL_LoanDetails WHERE loan_id IN( SELECT TOP 1 loan_id FROM Loan_GL_LoanDetails WHERE loan_id ='35' Order by ld_id DESC) . I tried this code also but all the rows are deleted want to delete the last row only ld_id is table-2 primary key loan_id is table 1 primary key & table 2 forign key

Biswa Pujarini Mohapatra · Answer

DELETE FROM Loan_GL_LoanDetails where Loan_id='35' order by Loan_id desc Limit 1 I hope you want to delete the record from details table only if not tell me or else you can use LAST() function to get last row

Biswa Pujarini Mohapatra · Answer

you can write more than one column in your subquery. so please give the specific id instead of * ex: DELETE FROM Loan_GL_LoanDetails Where loan_id IN(SELECT TOP 1 Ld.Loan_id FROM Loan_GL_LoanDetails LD Inner Join Loan_GL_LoanMaster LM on LM.loan_id=LD.loan_id where LM.loan_no = @loan_no ORDER BY LD.ld_id DESC)