Tuesday, June 18, 2019

SQL SELECT / UPDATE formatting trick

Many times I'm using this very simple SQL formatting for both SELECT & UPDATE (usually with MS SQL Server using SQL Server Management Studio or some other tool, interactively):

SELECT Count(*) -- TOP 20 A.*
-- UPDATE A SET A.Field1=B.Field1
FROM A INNER JOIN B ON A.ID=B.ID
WHERE <condition>

By default, all query is just a SELECT Count(*) which doesn't change anything but give you an idea of the number of records selected or updated later.

If I comment out the first part to something like this:

SELECT /*Count(*) --*/ TOP 20 A.*

it gives me some records that will be updated.

Then just undo the change and select the query from the UPDATE and execute the selected text. Also, the number of records updated should match the number displayed with Count(*).

It's like 3-in-1, which can easily go in comments of the ticketing system or emails.

No comments:

Post a Comment