This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Prepared by Bihag Thaker
Good Indentation makes queries more readable and easy to debug. Try to break the queries into different lines by different query clauses like UPDATE, FROM, JOIN, ON, WHERE etc.
Wherever possible, place targeted column names in separate lines one level indented to the right side. If a column is not the first column in the list, place comma (,) separator before the name of a column.
This helps queries easier to read, debug and maintain. While debugging a query, anytime any line containing a column name can be easily commented out or uncommented as per the need.
Following is an example of properly indented code for UPDATE statement:
12345678 UPDATE [dbo].[tbl_Customers]SETFirstName = 'FirstNameUpdated',LastName = 'LastNameUpdated',BirthDate = '19841231',EmailAddress = 'EmailAddressUpdated@domain.com',Gender = 'M'WHERE CustomerID = 'CUST001'
While writing UPDATE queries, make sure that the correct set of rows will be updated by including proper WHERE clause. UPDATE operation once committed cannot be undone.
Writing and executing SELECT query with the similar WHERE criteria as in UPDATE statement on the table should be exercised before implementing actual UPDATE queries. In this way, it can be confirmed that the correct set of rows will be updated.
When it is required to return updated records to the calling application with old values and new updated values, consider using OUTPUT clause in the UPDATE statement rather than using two separate UPDATE and SELECT statements to retrieve ‘just-updated’ records as shown below:
12345678910111213141516171819 UPDATE [dbo].[tbl_Customers]SETFirstName = 'FirstName',LastName = 'LastName',BirthDate = '20111212',EmailAddress = 'EmailAddress',Gender = 'M'OUTPUTdeleted.FirstName AS Old_FirstName,inserted.FirstName AS New_FirstName,deleted.LastName AS Old_LastName,inserted.LastName AS New_LastName,deleted.BirthDate AS Old_BirthDate,inserted.BirthDate AS New_BirthDate,deleted.EmailAddress AS Old_EmailAddress,deleted.EmailAddress AS New_EmailAddress,deleted.Gender AS Old_Gender,inserted.Gender AS New_GenderWHERE CustomerID='CUST006'
Leave a Reply