Please help to correct the query..

CREATE TABLE dbo.MyTable
(
     MasterIp varchar(255) NOT NULL
          CONSTRAINT PK_MyTable PRIMARY KEY
     ,status1 tinyint NOT NULL
     ,status2 tinyint NOT NULL
     ,Active_status tinyint NOT NULL
)
GO


CREATE TRIGGER dbo.TR_MyTable_IU
ON dbo.MyTable
AFTER INSERT, UPDATE
AS

IF @@ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;

-- Rows with parents
UPDATE T
SET status2 = COALESCE(P.status2, T.status2)
     ,Active_status = COALESCE(P.Active_status, T.Active_status)
FROM MyTable T
     JOIN inserted I
          ON T.MasterIp = I.MasterIp
     OUTER APPLY
     (
          SELECT TOP 1 status2, Active_status
          FROM MyTable T1
          WHERE T.MasterIp LIKE T1.MasterIp + '.%'
              AND T.MasterIp > T1.MasterIp
          ORDER BY T1.MasterIp DESC
     ) P;

-- Rows with Children
UPDATE C
SET status2 = I.status2
     ,Active_status = I.Active_status
FROM inserted I
     JOIN MyTable C
          ON C.MasterIp LIKE I.MasterIp + '.%'
          AND CHARINDEX('.', C.MasterIp, LEN(I.MasterIp) + 2 ) = 0
GO

INSERT INTO dbo.MyTable
VALUES('5.46.200.1.462222.2.1.1.1.1.1', 0, 0, 1);

select * from MyTable;

INSERT INTO dbo.MyTable
VALUES ('5.46.200.1.462222.2.1.1.1.1.1.1', 0, 1, 1);

select * from MyTable;

UPDATE dbo.MyTable
SET status2 = 3
     ,Active_status = 3
WHERE MasterIp = '5.46.200.1.462222.2.1.1.1.1.1';

select * from MyTable;

INSERT INTO dbo.MyTable
VALUES('5.46.200.1.462222.2.1', 0, 9, 9);

select * from MyTable;

updated to parent again and updated the parent and grand parent also

UPDATE dbo.MyTable
SET status2 = 1
     ,Active_status = 3
WHERE MasterIp = '5.46.200.1.462222.2.1.1.1.1.1';

select * from MyTable;

Results after update

MasterIp	            		status1	status2	Active_status
5.46.200.1.462222.2.1	        	0	9	9
5.46.200.1.462222.2.1.1.1.1.1		0	9	9
5.46.200.1.462222.2.1.1.1.1.1.1		0	1	3

Expected Results:

MasterIp	                      status1	status2	Active_status
5.46.200.1.462222.2.1	                0	9	  9
5.46.200.1.462222.2.1.1.1.1.1	        0	1	  3
5.46.200.1.462222.2.1.1.1.1.1.1	        0	1	  3

I could not able to align the output..

Submitted under: Microsoft SQL Server · TSQL ·  ·  ·