CTE

  • marlon.seton

    SSCrazy

    Points: 2623

    Koen Verbeeck (9/25/2013)


    Very interesting question. Never used a CTE with a DELETE statement before.

    Ditto. I wonder what happens if the CTE is built from more than one table?

  • TomThomson

    SSC Guru

    Points: 104773

    marlon.seton (9/30/2013)


    Koen Verbeeck (9/25/2013)


    Very interesting question. Never used a CTE with a DELETE statement before.

    Ditto. I wonder what happens if the CTE is built from more than one table?

    The same as for deleting through a view built from more than one table, as far as I can tell (except of course that you can't get round the rules by suplying an instead of delete trigger), but there may be some diferences. See . It would be nice if it were clearly documented whether there were any differences and if there were differences what they were, but if such documentation exists I haven't seen it. If you read an earlier comment on this, http://www.sqlservercentral.com/Forums/FindPost1498357.aspx, you would n't be asking.

    Tom

  • T.Ashish

    SSCarpal Tunnel

    Points: 4246

    good one.

  • jfgoude

    SSCrazy

    Points: 2586

    Update works well in CTE for updating underlayed table too.

    Nice and easy questions Thanks.

    really easy as there is NO transparent CR/LF invisible caracters ! 🙂

  • Joe Kelly-8908

    SSC-Addicted

    Points: 445

    What am I missing here ??

    Will "delete from ABC_CTE where id = 1;" not just delete the one record where id = 1 ??

    JK


    Tks,

    JK

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Joe Kelly-376311 (10/22/2013)


    What am I missing here ??

    Will "delete from ABC_CTE where id = 1;" not just delete the one record where id = 1 ??

    JK

    Yes, it will. You are not missing anything. Four rows are inserted, one is deleted - three remain, which is the correct answer.

    The point of the question is that you cannot just delete directly from a table, but also through a CTE - exactly the same way as you can delete rows through a view (and with the same rules and restrictions).


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • PHYData DBA

    SSCertifiable

    Points: 7541

    Hugo Kornelis (10/22/2013)


    Joe Kelly-376311 (10/22/2013)


    What am I missing here ??

    Will "delete from ABC_CTE where id = 1;" not just delete the one record where id = 1 ??

    JK

    Yes, it will. You are not missing anything. Four rows are inserted, one is deleted - three remain, which is the correct answer.

    The point of the question is that you cannot just delete directly from a table, but also through a CTE - exactly the same way as you can delete rows through a view (and with the same rules and restrictions).

    Hugo, Maybe we should do this as PM but are you able to use Locking hints when deleting through a CTE?

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    PHYData DBA (10/22/2013)


    Hugo Kornelis (10/22/2013)


    Joe Kelly-376311 (10/22/2013)


    What am I missing here ??

    Will "delete from ABC_CTE where id = 1;" not just delete the one record where id = 1 ??

    JK

    Yes, it will. You are not missing anything. Four rows are inserted, one is deleted - three remain, which is the correct answer.

    The point of the question is that you cannot just delete directly from a table, but also through a CTE - exactly the same way as you can delete rows through a view (and with the same rules and restrictions).

    Hugo, Maybe we should do this as PM but are you able to use Locking hints when deleting through a CTE?

    I have no idea; never tried it.

    Maybe you can run a simple test and share the results? (I could do it too, but I don't have the time available at this time)


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Joe Kelly-8908

    SSC-Addicted

    Points: 445

    Ah that's what I was missing ... the question was how many left in table, NOT how many deleted, Doh!


    Tks,

    JK

Viewing 9 posts - 61 through 69 (of 69 total)

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