2

I'm been beating my head up against a wall with this all day.

I have a SQL Server table called Accounts

CREATE TABLE [dbo].[Accounts](
    [IDNum] [INT] NULL,
    [FirstName] [VARCHAR](50) NULL,
    [LastName] [VARCHAR](50) NULL)


    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (123, N'Joe', N'McDaniels')
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (124, N'Mary', N'Cain')
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (125, N'John', N'Johnson')
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (126, N'Sue', N'Bird')
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (127, N'Michael', N'Bennett')
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (128, N'Larry ', N'Jackson')
    GO
 INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (128, 0, 0)
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (129, N'0', N'0')
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (130, N'Tanya', N'Hall')
    GO
 INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (130, 0, 0)
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (131, N'0', N'0')
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (132, N'Joe', N'Johnson')
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (133, N'Stephen', N'Cole')
    GO

I'm trying to eliminate the duplicate rows with 0 as the FirstName and LastName.

The non duplicate rows with zeros I want to keep. I want to eliminate only the rows that contain zeros that have a duplicate IDNum.

I can't add an a primary key column.

My first approach was to find the duplicates, put in table variable, filter the zero rows then union that back with the table excluding those ID numbers. Then I thought it has to be a better way. Any help?

End Result

enter image description here

0
3

If I understand your question correctly, how about something like this:

DROP TABLE IF EXISTS dbo.Accounts
go
CREATE TABLE [dbo].[Accounts](
    [IDNum] [INT] NULL,
    [FirstName] [VARCHAR](50) NULL,
    [LastName] [VARCHAR](50) NULL)


INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (123, N'Joe', N'McDaniels')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (124, N'Mary', N'Cain')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (125, N'John', N'Johnson')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (126, N'Sue', N'Bird')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (127, N'Michael', N'Bennett')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (128, N'Larry ', N'Jackson')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (128, 0, 0)
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (129, N'0', N'0')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (130, N'Tanya', N'Hall')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (130, 0, 0)
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (131, N'0', N'0')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (132, N'Joe', N'Johnson')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (133, N'Stephen', N'Cole')
GO

;WITH _dups
AS (
    SELECT IDNum
        ,count(*) cnt
    FROM accounts
    GROUP BY IDNum
    HAVING count(*) > 1
    )
--now join _cte to the real table to delete the one's with zeros in First and Last name
DELETE a
FROM accounts a
JOIN _dups d
    ON d.idnum = a.idnum
        AND a.firstname = '0'
        AND a.lastname = '0'

SELECT *
FROM accounts

OR

DROP TABLE IF EXISTS dbo.Accounts
go
CREATE TABLE [dbo].[Accounts](
    [IDNum] [INT] NULL,
    [FirstName] [VARCHAR](50) NULL,
    [LastName] [VARCHAR](50) NULL)


INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (123, N'Joe', N'McDaniels')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (124, N'Mary', N'Cain')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (125, N'John', N'Johnson')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (126, N'Sue', N'Bird')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (127, N'Michael', N'Bennett')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (128, N'Larry ', N'Jackson')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (128, 0, 0)
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (129, N'0', N'0')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (130, N'Tanya', N'Hall')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (130, 0, 0)
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (131, N'0', N'0')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (132, N'Joe', N'Johnson')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (133, N'Stephen', N'Cole')
GO

DELETE a
FROM accounts a
WHERE FirstName = '0'
    AND LastName = '0'
    AND EXISTS (
        SELECT *
        FROM accounts
        WHERE IDNum = a.IDNum
            AND FirstName <> '0'
            AND LastName <> '0'
        )

SELECT *
FROM accounts

Both of these solutions produce the results you're looking for.

| IDNum | FirstName | LastName  |
|-------|-----------|-----------|
| 123   | Joe       | McDaniels |
| 124   | Mary      | Cain      |
| 125   | John      | Johnson   |
| 126   | Sue       | Bird      |
| 127   | Michael   | Bennett   |
| 128   | Larry     | Jackson   |
| 129   | 0         | 0         |
| 130   | Tanya     | Hall      |
| 131   | 0         | 0         |
| 132   | Joe       | Johnson   |
| 133   | Stephen   | Cole      |

UPDATE 8/2/18 - Excluding records instead of deleting them.

Based on your comment

What about excluding those records from the resultset? So don't delete them just return the others., see if this gives you what you want.

;with ExcludeTheseRecords as 
(
select a.*
FROM accounts a
WHERE FirstName = '0'
    AND LastName = '0'
    AND EXISTS (
        SELECT *
        FROM accounts
        WHERE IDNum = a.IDNum
            AND FirstName <> '0'
            AND LastName <> '0'
        )
)

SELECT * from accounts 
except
select * from ExcludeTheseRecords
3
  • @KyleJohnson - I updated my answer to use the sample data you provided after my initial response. The results appear to be what you're looking for, but perhaps I'm missing something in your question. Jul 27 '18 at 9:13
  • What about excluding those records from the resultset? So don't delete them just return the others. Aug 2 '18 at 15:02
  • @KyleJohnson - I added an example of excluding records from the results instead of deleting them. Aug 2 '18 at 15:13
3

Another approach would be to:

  1. Partition the rows by IDNum
  2. Number the rows within each partition (rows with the same IDNum) so that rows with '0' FirstName and LastName come last
  3. Delete rows numbered greater than one

This ensures that '0' rows are only kept if there is no non-'0' row for the same IDNum.

DELETE D 
FROM
(
    SELECT
        r = ROW_NUMBER() OVER (
            PARTITION BY A.IDNum
            ORDER BY 
                CASE 
                    WHEN A.FirstName = '0' AND A.LastName = '0' THEN 1 
                    ELSE 0 
                END ASC)
    FROM dbo.Accounts AS A
) AS D
WHERE D.r > 1;

Demo: db<>fiddle

delete execution plan

2

Here is your solution as far as I could understand your requirements. It's a straight forward English-to-SQL translation of the statement:

"Delete all rows that have 0 for both first and last name, and for which there exists another row in the same table (Aliased A2), with the same IDNum, but with First name and last name that are different than zero":

DELETE FROM Accounts
WHERE   Accounts.FirstName = '0'
        AND
        Accounts.LastName = '0'
        AND
        EXISTS  ( 
                    SELECT  NULL
                    FROM    Accounts AS A2
                    WHERE   A2.IDNum = Accounts.IDNum
                            AND
                            A2.FirstName <> '0'
                            AND
                            A2.LastName <> '0'
                    );      
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.