CTE DML

Question

CTE DML

Viewing 4 posts - 31 through 33 (of 33 total)

You must be logged in to reply to this topic. Login to reply

rhythmk SSCertifiable Points: 7271 More actions · Answer 1

BWFC (6/17/2014)
rhythmk (6/17/2014)
BWFC (6/17/2014)
rhythmk (6/17/2014)
Thanks for question.It reminds me the trick to delete the duplicate records in a table 🙂
Could you enlighten me on that please? It sounds very useful.
Here you go.
CREATE TABLE #TEMP (ID INT,FNAME VARCHAR(50),LNAME VARCHAR(50))
GO
INSERT INTO #TEMP
SELECT 1,'LIONEL','MESSI' UNION ALL
SELECT 1,'LIONEL','MESSI' UNION ALL
SELECT 1,'LIONEL','MESSI' UNION ALL
SELECT 2,'NEYMAR','SILVA' UNION ALL
SELECT 2,'NEYMAR','SILVA' UNION ALL
SELECT 3,'CHRISTIANO','RONALDO' UNION ALL
SELECT 3,'CHRISTIANO','RONALDO' UNION ALL
SELECT 4, 'THOMAS','MUELLER'
GO
--SELECT * FROM #TEMP
;WITH DUPL AS
(SELECT ID,FNAME,LNAME,ROW_NUMBER() OVER (PARTITION BY ID,FNAME,LNAME ORDER BY ID,FNAME,LNAME) [RN]
FROM #TEMP)
DELETE FROM DUPL WHERE RN <> 1
--SELECT * FROM #TEMP
--DROP TABLE #TEMP
I like that, there's another one to add to the toolbox. Thanks.

Thank you.

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

barry.mcconnell Default port Points: 1447 More actions · Answer 2

In my experience the most common use for CTE is to replace a subselect and use the CTE in a join. The advantage is that a CTE typically only runs once rather than once per candidate row of the answer set (as in a subselect). For example, if you wanted to calculate a percentage on each row you might use a CTE to calculate the denominator and then join it to each detail row that has the numerator. With the expansion of the OVER clause this is not necessary any longer but that's a different story.

Very simple example:

WITH calc_denominator AS (SELECT sum(somevalue) AS denom FROM mytable)

SELECT somevalue

,somevalue/d.denom AS pct_of_total

FROM mytable FULL OUTER JOIN calc_denominator d

There are no facts, only interpretations.
Friedrich Nietzsche

john.moreno Default port Points: 1485 More actions · Answer 3

Did you intend for the WITH with a GO before it to be a red herring? I knew you could do the insert, but the question seemed simple and I suspected a trap...and that was the only thing that seemed likely, but it was the first statement in the batch....

Here's a SQLFiddle version without the GOs

http://sqlfiddle.com/#!6/d41d8/19553

barry.mcconnell Default port Points: 1447 More actions · Answer 4

No, that GO was just to ensure that the table creation and loading was complete before attempting the query. I prefer to keep my DDL, data loading and queries in separate batches.

There are no facts, only interpretations.
Friedrich Nietzsche