How do I update table from same table column using stored procedure

See more:

SQL

SQL-Server

I have one table tblLotTransfer structure is like this

Id	FrLotNo	ToLotNo	MainLotNo	LotUsed
1	22100001	22100003	22100001	1
2	22100001	22100004	22100001	1
3	22100001	22100005	22100001	1
4	22100002	22100006	22100002	1
5	22100003	22100007	22100001	0

1 st condition
Check if frLotNo 22100002 is not available in ToLotNo column then Insert mainlotNo same like frLotNo frLotNo = MainLotNo

2 condition
Check if frLotNo 22100003 is available in ToLotNo column then Insert mainlotNo where tuple found in row here we found 22100003 in first row so copy mainlono from that row and insert at row 5 and column mainLotNo

What I have tried:

I have write stored procedure for this but it does not satisfy second condition correctly. Can i use cursor for this?

ALTER PROCEDURE [dbo].[SP_LotTransfer_Save] @LId int = NULL,
@LDate datetime = NULL,
@LTransId int = NULL,
@ItemId int = NULL,
@LOperationId int = NULL,
@LFrLotNo varchar(20) = NULL,
@LToLotNo varchar(20) = NULL,
@MainLotNo varchar(20) = NULL,
@LTransferWt float = NULL,
@LIssuePr float = NULL,
@FrKarigarId int = NULL,
@ToKarigarId int = NULL,
@CreatedBy varchar(50),
@HIsOpening bit = 1,
@ActionType varchar(25)
AS
  DECLARE @MaxLotNo varchar(20)
  DECLARE @MainOLotNo varchar(20)
  DECLARE @MaxId int
  DECLARE @Rec_Cnt AS integer
  DECLARE @Main_Cnt AS integer
  BEGIN

    SET NOCOUNT ON;

    IF @ActionType = 'SaveData'
    BEGIN

    BEGIN TRY
      BEGIN TRAN
        SELECT
          @MaxId = ISNULL(MAX(LotTransferId), 0) + 1
        FROM tblLotTransfer

        SELECT
          @MaxLotNo = ISNULL(MAX(MaxLotNo), 0) + 1
        FROM vwGetMaxLotNo

        PRINT @MaxLotNo

        SET @LTransId = @LTransId + 1

        SELECT
          @Rec_Cnt = COUNT(ToLotNo)
        FROM tblLotTransfer
        WHERE ToLotNo IN (SELECT DISTINCT
          FrLotNo tblLotTransfer
        FROM tblLotTransfer
        WHERE FrLotNo = LTRIM(RTRIM(@LFrLotNo)))

        PRINT @Rec_Cnt

        --Main lot No Start
        IF @Rec_Cnt = 0
        BEGIN
          INSERT INTO tblLotTransfer (LotTransferId, LotTransferDt, TransactionId, ItemId, OperationId, FrLotNo, ToLotNo, MainLotNo, TransferWt, IssuePr, FrKarigarId, ToKarigarId, CreatedBy, IsOpening)
            VALUES (@MaxId, CONVERT(datetime, @LDate, 105), @LTransId, @ItemId, @LOperationId, @LFrLotNo, @MaxLotNo, @LFrLotNo, CAST(@LTransferWt AS float), CAST(@LIssuePr AS float), @FrKarigarId, @ToKarigarId, @CreatedBy, @HIsOpening)
        END
        --Main lot No End

        SELECT
          @Main_Cnt = COUNT(ToLotNo)
        FROM tblLotTransfer
        WHERE ToLotNo IN (SELECT DISTINCT
          FrLotNo tblLotTransfer
        FROM tblLotTransfer
        WHERE FrLotNo = LTRIM(RTRIM(@LFrLotNo)))

        IF @Main_Cnt > 0

        BEGIN

          UPDATE t
          SET t.MainLotNo = (SELECT
            MainLotNo
          FROM tblLotTransfer
          WHERE ToLotNo IN (SELECT DISTINCT
            FrLotNo tblLotTransfer
          FROM tblLotTransfer t2
          WHERE FrLotNo = LTRIM(RTRIM(@LFrLotNo))))
          FROM tblLotTransfer t
          WHERE t.LotTransferId = @MaxId
        END
      
      COMMIT TRAN

    END TRY

    BEGIN CATCH
      IF @@TRANCOUNT > 0
      BEGIN
        ROLLBACK TRAN
        INSERT INTO dbo.DB_Errors
          VALUES (SUSER_SNAME(), ERROR_NUMBER(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_LINE(), ERROR_PROCEDURE(), ERROR_MESSAGE(), GETDATE());
      END
    END CATCH
    END
    ELSE

  
  END