CTE DML

  • Comments posted to this topic are about the item CTE DML

    There are no facts, only interpretations.
    Friedrich Nietzsche

  • 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

  • I've never thought about insert data in a CTE

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

  • 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 http://www.sqlservercentral.com/articles/Best+Practices/61537

  • Thanks for QOD, Barry.

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

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

  • I hate DISTINCT *

    😛

  • 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

  • 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’! **
  • 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
    🙂

  • This was removed by the editor as SPAM

  • 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 http://www.sqlservercentral.com/articles/Best+Practices/61537

  • 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