By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create TABLE [dbo].[VL_Wohneinheiten](
[VLW_ID] [int] NOT NULL,
[VLW_Liegenschaft] [int] NOT NULL,
[VLW_FlatID] [nvarchar](25) NOT NULL,
[VLW_OTOID] [nvarchar](25) NOT NULL)
Create TABLE [dbo].[VL_Liegenschaften](
[VLI_ID] [int] NOT NULL,
[VLI_Strasse] [nvarchar](100) NOT NULL,
[VLI_Nummer] [nvarchar](50) NOT NULL,
[VLI_PLZ] [nvarchar](10) NOT NULL,
[VLI_Ort] [nvarchar](100) NOT NULL,
[VLI_Import_flag] [tinyint] NOT NULL,
[VLI_OMDF_ID] [varchar](20) NULL)
INSERT [dbo].[VL_Wohneinheiten] ([VLW_ID], [VLW_Liegenschaft], [VLW_FlatID], [VLW_OTOID])
VALUES (25822, 79045, N'', N'B.111.253.375.3')
INSERT [dbo].[VL_Wohneinheiten] ([VLW_ID], [VLW_Liegenschaft], [VLW_FlatID], [VLW_OTOID])
VALUES (25823, 79046, N'', N'B.111.253.375.3')
INSERT [dbo].[VL_Wohneinheiten] ([VLW_ID], [VLW_Liegenschaft], [VLW_FlatID], [VLW_OTOID])
VALUES (21638, 74861, N'', N'B.111.253.375.3')
INSERT [dbo].[VL_Wohneinheiten] ([VLW_ID], [VLW_Liegenschaft], [VLW_FlatID], [VLW_OTOID])
VALUES (21639, 74862, N'', N'B.111.253.375.3')
4 rows affected
INSERT [dbo].[VL_Liegenschaften] ([VLI_ID], [VLI_Strasse], [VLI_Nummer], [VLI_PLZ], [VLI_Ort], [VLI_Import_flag], [VLI_OMDF_ID])
VALUES (74861, N'Zürcherstrasse', N'210', N'8500', N'Frauenfeld2', 0, NULL)
INSERT [dbo].[VL_Liegenschaften] ([VLI_ID], [VLI_Strasse], [VLI_Nummer], [VLI_PLZ], [VLI_Ort], [VLI_Import_flag], [VLI_OMDF_ID])
VALUES (74862, N'Zürcherstrasse', N'210', N'8500', N'Frauenfeld2', 0, NULL)
INSERT [dbo].[VL_Liegenschaften] ([VLI_ID], [VLI_Strasse], [VLI_Nummer], [VLI_PLZ], [VLI_Ort], [VLI_Import_flag], [VLI_OMDF_ID])
VALUES (79045, N'Zürcherstrasse', N'210A', N'8500', N'Frauenfeld2', 0, N'FRA_12')
INSERT [dbo].[VL_Liegenschaften] ([VLI_ID], [VLI_Strasse], [VLI_Nummer], [VLI_PLZ], [VLI_Ort], [VLI_Import_flag], [VLI_OMDF_ID])
VALUES (79046, N'Zürcherstrasse', N'210A', N'8500', N'Frauenfeld2', 0, N'FRA_12')
4 rows affected
delete w from [dbo].[VL_Liegenschaften] w
inner join
(select *,row_number() over(partition by VLW_OTOID order by
VLW_Liegenschaft desc) as rn from VL_Wohneinheiten as t1
INNER JOIN VL_Liegenschaften as t2 ON t1.VLW_Liegenschaft = t2.VLI_ID
) a on a.[VLI_ID]=w.[VLI_ID] where rn<>1
3 rows affected