August 2011 Entries

The last thing anyone wants to do is mess up an UPDATE statement! Here's a quick refresher for those of you using SQL Server:

Simplest case (a single row, in a single table with a single known value in a column):

UPDATE YourTableName
SET Column1 = ‘NewValue’
WHERE SameOrOtherColumn = ‘OldValue’

Most common case (multiple rows, using another table as the source data with multiple columns):

UPDATE YourTableName
SET ColumnX = OtherTable.Column1,
         ColumnY = OtherTable.Column2
FROM OtherTable
WHERE YourTableName.Column1 = OtherTable.Column1

Advanced case (multiple rows, multiple tables reliant on a subquery)

  UPDATE Target_Table
   SET col2 = z.colB,
       col3 = z.colC,
       col4 = z.colD,
       col5 = z.colE
     FROM
       (select x.col_a,
               x.col_b,
               y.col_b,
               y.col_b
            from tbl_1 x, tbl_2 y
           where x.col_c = y.col_c
        ) z
     WHERE Target_Table.col1 = z..colA
  

Think relational algebra for this implementation--basically, what you are doing in the subselect is gathering the data you need from the necessary tables, then redefining the results as 'z'. Once you've done that, you are assigning the target columns the values retrieved back from your subquery.

 

 

To quickly look through your stored procedure objects for a text value in a database instance, do the following:

use [Metro]
go


SELECT ROUTINE_NAME
  FROM INFORMATION_SCHEMA.ROUTINES
   WHERE ROUTINE_DEFINITION LIKE '%whatyouarelookingfor%'
     AND ROUTINE_TYPE = 'PROCEDURE' 
 

Now, this is no replacement for a configuration management repository, but it will do in a pinch. BTW, remember this sql statement does NOT transcend all databases on the server, just a single db instance.