September 30, 2013 at 2:07 am
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?
September 30, 2013 at 5:50 am
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
October 7, 2013 at 12:15 am
good one.
October 11, 2013 at 5:45 am
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 ! 🙂
October 22, 2013 at 3:07 am
October 22, 2013 at 2:57 pm
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).
October 22, 2013 at 3:07 pm
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?
October 22, 2013 at 4:04 pm
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)
October 22, 2013 at 5:53 pm
Viewing 9 posts - 61 through 68 (of 68 total)
You must be logged in to reply to this topic. Login to reply