Ninja's_RGR'us (6/27/2011)
mister.magoo (6/27/2011)And the same thing re-written using a CTE - just to show an alternate way to format the code:
;WITH ToDelete AS
(
SELECT TOP (9) *
FROM @t AS t
ORDER BY
NEWID()
)
DELETE ToDelete
OUTPUT deleted.*
Why did I bother posting this?.... well, I like this construct for updates mostly as the visual seperation of the SELECT from the UPDATE or the DELETE makes it entirely clear what you are doing - Here is the set of data I am interested in....now here is what I am going to do to it.
I think it's much clearer that way and it makes it a little easier and less risky to move between select and delete without having a brainfart!
😀 I always enjoy words like that - hope they still make me chuckle when I am 90!
I tend to show people who are less experienced how to do it this way for precisely that reason. They can write the SELECT on it's own, confirm that it returns the correct set of data and then simply "wrap it all up" in a CTE and perform the update/delete.
I also like to get them to pop an OUTPUT clause in there and wrap it all in a transaction with rollback to check the results are really correct before they commit - and to be honest, if it's production DB - that's the way I operate as well (mostly).
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);