CTE DML

  • barry.mcconnell

    Default port

    Points: 1447

    Comments posted to this topic are about the item CTE DML

    There are no facts, only interpretations.
    Friedrich Nietzsche

  • Koen Verbeeck

    SSC Guru

    Points: 258909

    Easy one (as I recently read this blogpost: Change data through CTE’s[/url]). Thanks for the question!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • domenico.delbrocco

    Mr or Mrs. 500

    Points: 586

    I've never thought about insert data in a CTE

  • rhythmk

    SSCertifiable

    Points: 7162

    Thanks for question.It reminds me the trick to delete the duplicate records in a table 🙂

    --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
    🙂

  • Neil Burton

    SSC-Insane

    Points: 21900

    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.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help [/url]

  • free_mascot

    One Orange Chip

    Points: 27168

    Thanks for QOD, Barry.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • danielfountain

    SSCarpal Tunnel

    Points: 4229

    I never even considered using a CTE for an insert..... interesting.

  • palotaiarpad

    SSCarpal Tunnel

    Points: 4871

    I hate DISTINCT *

    😛

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    Koen Verbeeck (6/17/2014)


    Easy one (as I recently read this blogpost: Change data through CTE’s[/url]). Thanks for the question!

    +1

    Thanks too

    Igor Micev,
    My blog: www.igormicev.com

  • HanShi

    SSC-Dedicated

    Points: 33186

    palotaiarpad (6/17/2014)


    I hate DISTINCT *

    😛

    "hate" is a strong word, but in this QOTD the DISTINCT is absolutely not necessary.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • rhythmk

    SSCertifiable

    Points: 7162

    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

    --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
    🙂

  • Ed Wagner

    SSC Guru

    Points: 286957

    CTEs solve so many problems. Thanks for the question.

  • This was removed by the editor as SPAM

  • Neil Burton

    SSC-Insane

    Points: 21900

    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.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help [/url]

  • SqlMel

    SSCrazy

    Points: 2891

    I actually thought "select distinct * ..." might raise an error.

    I have never used this and wonder why it would even be allowed.

    ---------------
    Mel. 😎

Viewing 15 posts - 1 through 15 (of 34 total)

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