Why this code works well in MySQL but not in SQL Server?

Question

This is a question in Leetcode: https://leetcode.com/problems/delete-duplicate-emails/solution/

Basically the question asks to delete duplicate emails and keep the one that has the minumal index.

My code is the following one, and I firstly ran it in SQL Server mode, and it shows "Internal Error". But it has passed in MySQL mode. Why does this happen? Are there any syntax differences between them shown in this case? Thanks.

delete from Person
where Id not in (
select temp.minimum from 
(select min(Id) as minimum from Person group by Email) as temp
);

Yeah. SQL is not SQL - it is likely the most NOT standardized langauge ever created. In practice every product has a very different dialect. — TomTom
– TomTom, Commented Feb 20, 2020 at 14:07
I thought the MS SQL syntax looked OK. It also checks up all OK in SSMS — Cato
– Cato, Commented Feb 20, 2020 at 14:10
@60221611 This triply-nested query is really bad - it forces a full table scan to calculate the GROUP BY and HAVING, then another seek (I hope) to find the rows to delete. The "accepted" solution in leetcode does a full self-join on email, which could be quite clean. In MySQL 8/SQL Server, the CTE and ROW_Number will find the non-duplicates in a single pass. You'll have to compare exeuction plans for both queries (accepted and ROW_NUMBER) to find which is best — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Feb 20, 2020 at 14:24

Yogesh Sharma · Accepted Answer · 2020-02-20 14:11:04Z

1

In SQL Server you use CTE with ROW_NUMBER() :

WITH cte AS (
     SELECT P.*, ROW_NUMBER() OVER (PARTITION BY p.email ORDER BY id) AS seq
     FROM Person P
)
DELETE
FROM cte c
WHERE c.seq > 1;

However, your original version of query should also work in SQL Server.

edited Feb 20, 2020 at 14:11

answered Feb 20, 2020 at 14:07

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rick James Over a year ago

Does this work? It is DELETEing from the pseuo-table created by WITH (cte).

LongChalk_Rotem_Meron · Accepted Answer · 2020-02-20 14:17:38Z

0

Looks good to me. Perhaps

DELETE FROM Person
WHERE Id not in ( Select Id FROM
      (SELECT Email,  
        MIN(Id) as Id, COUNT(*) as N 
        FROM Person 
        GROUP BY Email
        HAVING COUNT(*) > 1) as temp
);

Make sure to run the inner SELECT by itself to see it's the same schema.

answered Feb 20, 2020 at 14:17

LongChalk_Rotem_Meron

81311 silver badges14 bronze badges

Comments

sacse · Accepted Answer · 2020-02-20 15:03:57Z

0

I tried this on sql server express 10.50.6560 and it seems it works...

CREATE TABLE PERSON
(
  ID INT NOT NULL
, EMAIL VARCHAR(1000)
)

ALTER TABLE PERSON
ADD CONSTRAINT PK_PERSON
PRIMARY KEY (ID)


INSERT INTO PERSON VALUES(1, 'AAAAA')
INSERT INTO PERSON VALUES(2, 'AAAAA')
INSERT INTO PERSON VALUES(3, 'BBBBB')

DROP TABLE PERSON

--Your query

delete from Person
where Id not in (
select temp.minimum from 
(select min(Id) as minimum from Person group by Email) as temp
);

It deletes one row...

Hope it helps

edited Feb 20, 2020 at 15:03

sacse

3,8272 gold badges19 silver badges27 bronze badges

answered Feb 20, 2020 at 14:20

AndreaM

314 bronze badges

Collectives™ on Stack Overflow

Why this code works well in MySQL but not in SQL Server?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related