I have been having hassles trying to get microsoft SQL server management 2017 to mark as inactive (column Active set to 0) database rows which are exact duplicates when it comes to [DESCRIPTION], [MANUFACTURER], [MODEL], [VERSION], [IDNUMBER] (no rows are absolute duplicates). I got two approaches working, but both do the same thing: Keep only the unique rows (example 2) or the rows with only 1 duplicate as well (example 1). What I WANT to do is also mark , for example, 9 of the 10 duplicate rows as Active=0 while leaving the other 1 row as Active=1 as well as leaving as Active=1 the unique rows.
Does anybody have an idea on how I would do that? I would be ever so grateful.
/****** marks Active to 0 on columns with exact duplicates ALL OF THEM, leaves unique procs as "1" *****/
UPDATE [ProCal].[dbo].[Proc_QRef]
SET [Active] = 0 WHERE [DESCRIPTION] IN (
SELECT [DESCRIPTION] FROM (
SELECT
[DESCRIPTION]
,ROW_NUMBER() OVER (PARTITION BY [DESCRIPTION], [MANUFACTURER], [MODEL], [VERSION], [IDNUMBER] ORDER BY [DESCRIPTION]) AS [ItemNumber]
– Change the partition columns to include the ones that make the row distinct
FROM
[ProCal].[dbo].[Proc_QRef]
) a WHERE ItemNumber > 2 – Keep only the unique rows or the rows with only 1 duplicate
)
SELECT * FROM [ProCal].[dbo].[Proc_QRef]
ORDER BY [PROCEDURE]
PROCEDURE DESCRIPTION MANUFACTURER MODEL VERSION IDNUMBER Stock Code Active Date_Created Prev_Proc Created_By
850 Pressure Gauge Alicat P-100PSIG 0.9 1/08/2018 NULL 0 2019-04-16 14:15:45.000 NULL Eris
851 Audio Impedance Tester All Sun EM480B 1.00 30/01/2017 NULL 1 2019-04-16 14:16:00.000 NULL Eris
852 Audio Impedance Tester All Sun EM480B 1.00 30/01/2017 NULL 1 2019-04-16 14:16:16.000 NULL Eris
853 Digital Display Allen Bradley PannelView 1.01 11/06/2018 NULL 1 2019-04-16 14:16:31.000 NULL Eris
854 Digital Display Allen Bradley Versa View 1.01 11/06/2018 NULL 1 2019-04-16 14:16:46.000 NULL Eris
855 Power Quality Analyser All-Test Pro OLII 1.0 9/11/2016 NULL 0 2019-04-16 14:17:01.000 NULL Eris
856 Power Quality Analyser All-Test Pro OLII 1.0 9/11/2016 NULL 0 2019-04-16 14:17:17.000 NULL Eris
857 Power Quality Analyser All-Test Pro OLII 1.0 9/11/2016 NULL 0 2019-04-16 14:17:32.000 NULL Eris
858 Power Quality Analyser All-Test Pro OLII 1.0 9/11/2016 NULL 0 2019-04-16 14:17:48.000 NULL Eris
859 Power Quality Analyzer All-Test OL II 1.0 16/12/2015 NULL 0 2019-04-16 14:18:03.000 NULL Eris
860 Power Quality Analyzer All-Test OL II 1.0 16/12/2015 NULL 0 2019-04-16 14:18:18.000 NULL Eris
861 Power Quality Analyzer All-Test OL II 1.0 16/12/2015 NULL 0 2019-04-16 14:18:33.000 NULL Eris
862 Power Quality Analyzer All-Test OL II 1.0 16/12/2015 NULL 0 2019-04-16 14:18:48.000 NULL Eris
863 Airflow Hood Alnor Balometer 1.0 5/10/2017 NULL 0 2019-04-16 14:19:04.000 NULL Eris
864 Airflow Hood Alnor Balometer 1.0 5/10/2017 NULL 0 2019-04-16 14:19:19.000 NULL Eris
865 Airflow Hood Alnor Balometer 1.0 5/10/2017 NULL 0 2019-04-16 14:19:34.000 NULL Eris
866 Digital Anemometer Alnor RVD 1.0 02/02/2016 NULL 1 2019-04-16 14:19:50.000 NULL Eris
867 Digital Anemometer Alnor RVD 1.0 02/02/2016 NULL 1 2019-04-16 14:20:09.000 NULL Eris
868 Generic DC Power Supply Alpha Generic 1.01 8/02/2018 NULL 0 2019-04-16 14:20:20.000 NULL Eris
869 Generic DC Power Supply Alpha Generic 1.01 8/02/2018 NULL 0 2019-04-16 14:20:35.000 NULL Eris
870 Generic DC Power Supply Alpha Generic 1.01 8/02/2018 NULL 0 2019-04-16 14:20:50.000 NULL Eris
871 Generic DC Power Supply Alpha Generic 1.01 8/02/2018 NULL 0 2019-04-16 14:21:06.000 NULL Eris
/****** marks Active to 0 on columns with exact duplicates ALL OF THEM, leaves unique procs as “1” *****/
;WITH Duplicates AS
(
SELECT
[DESCRIPTION], [MANUFACTURER], [MODEL], [VERSION], [IDNUMBER]
,ROW_NUMBER() OVER (PARTITION BY [DESCRIPTION], [MANUFACTURER], [MODEL], [VERSION], [IDNUMBER] ORDER BY [DESCRIPTION]) AS [RowNum]
FROM
[ProCal].[dbo].[Proc_QRef]
)
UPDATE [ProCal].[dbo].[Proc_QRef]
SET Active = 0
FROM Duplicates d
WHERE d.[DESCRIPTION] = [ProCal].[dbo].[Proc_QRef].[DESCRIPTION]
AND d.RowNum > 1
PROCEDURE DESCRIPTION MANUFACTURER MODEL VERSION IDNUMBER Stock Code Active Date_Created Prev_Proc Created_By
18905 Outside Micromete (H Unit+Rods) Mitutoyo 500-600mm 1.00 4/07/2018 NULL 1 2019-04-17 17:17:57.000 NULL Eris
18906 Outside Micrometer (changeable Rod) Mitutoyo 400-500mm 1.00 4/07/2018 NULL 1 2019-04-17 17:17:57.000 NULL Eris
18907 Outside Micrometer (interchangeable anvi Mitutoyo 0mm-150mm 1.00 19/06/2018 NULL 0 2019-04-17 17:17:59.000 NULL Eris
18908 Outside Micrometer (interchangeable anvi Mitutoyo 0mm-150mm 1.00 19/06/2018 NULL 0 2019-04-17 17:17:59.000 NULL Eris
18909 Outside Micrometer (interchangeable anvi Mitutoyo 0mm-150mm 1.00 19/06/2018 NULL 0 2019-04-17 17:17:59.000 NULL Eris
18910 Outside Micrometer (interchangeable anvi Mitutoyo 0mm-150mm 1.00 19/06/2018 NULL 0 2019-04-17 17:17:59.000 NULL Eris
18911 Outside Micrometer (interchangeable anvi Mitutoyo 200mm300mm 1.20 22/03/2019 NULL 0 2019-04-17 17:18:01.000 NULL Eris
18912 Outside Micrometer (interchangeable) Mitutoyo 300mm400mm 1.1 22/03/2019 NULL 1 2019-04-17 17:18:03.000 NULL Eris
18913 Outside Micrometer (interchang > Mitutoyo 300mm400mm 1.00 28/03/2019 NULL 1 2019-04-17 17:18:03.000 NULL Eris
18914 Outside Micrometer (interchang > Mitutoyo 500mm600mm 1.00 29/03/2019 NULL 1 2019-04-17 17:18:04.000 NULL Eris
18915 Outside Micrometer (interchang > Mitutoyo 600mm700mm 1.00 29/03/2019 NULL 1 2019-04-17 17:18:04.000 NULL Eris
18916 Outside Micrometer (interchang > Mitutoyo 800mm900mm 1.00 29/03/2019 NULL 1 2019-04-17 17:18:04.000 NULL Eris
18917 Outside Micrometer Mitutoyo 0-150mm 1.0 5/07/2017 NULL 0 2019-04-17 17:18:04.000 NULL Eris